CODEWITHSUNDEEP
sqlsql-server
Peter
Peter

Reputation: 393

How to return only rows with maximum values

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.

Upvotes: 1

Views: 88

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think you are looking for the "mode" -- the most common value:

select w.*
from (select w.city, w.description, count(*) as cnt
             row_number() over (partition by city order by count(*) desc) as seqnum
      from weather w
      group by w.city, w.description
     ) w
where seqnum = 1;

Upvotes: 1

Serg
Serg

Reputation: 22811

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);

Upvotes: 3

Related Questions