如何仅返回具有最大值的行(How to return only rows with maximum values)

如何仅返回具有最大值的行(How to return only rows with maximum values)

这是我的SQL Server表：

CREATE TABLE weather (id INT NOT NULL IDENTITY PRIMARY KEY, place varchar(100) NULL, description varchar(100) NULL);

地点列包含城市名称。

描述包含天气描述（例如“晴天”）。

每个城市每小时插入多个值。 现在我想根据我收集的数据知道什么是“avarage”天气描述。

结果集可能如下所示：

place description --------------------- london sunny berlin rainy

这是我的SQL小提琴示例数据： http ： //sqlfiddle.com/#！6/ee736/2/0

我目前的陈述尚未完成：

SELECT w.place, w.description, COUNT(w.description) DESCRIPTION_COUNT FROM weather w GROUP BY w.place, w.description

该陈述错过了对地点进行分组并找到描述的最大数量。 我猜它可以通过使用HAVING和subselects来解决。

This is my SQL Server table:

CREATE TABLE weather (id INT NOT NULL IDENTITY PRIMARY KEY, place varchar(100) NULL, description varchar(100) NULL);

The place column contains a city name.

The description contains the weather description (e.g. "sunny").

Multiple values are inserted per hour per city. Now I want to know what is the "avarage" weather description, based on my collected data.

A result set could look like this:

place description --------------------- london sunny berlin rainy

Here is my SQL fiddle with sample data: http://sqlfiddle.com/#!6/ee736/2/0

My current statement, which is not completed yet:

SELECT w.place, w.description, COUNT(w.description) DESCRIPTION_COUNT FROM weather w GROUP BY w.place, w.description

This statement misses to group the place and find the maximum count on the description. I guess it can be solved by using HAVING and subselects.

最满意答案

你可以这样做

SELECT top(1) with ties w.place , w.description, COUNT(w.description) DESCRIPTION_COUNT from weather w group by w.place, w.description order by row_number() over (partition by place order by COUNT(w.description) desc);

You can do it this way

SELECT top(1) with ties w.place , w.description, COUNT(w.description) DESCRIPTION_COUNT from weather w group by w.place, w.description order by row_number() over (partition by place order by COUNT(w.description) desc);