How to return only rows with maximum values

Question

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.

Do you wish to retrieve the data for records which have maximum occurance of description for each city — Kapil
– Kapil, Commented Jul 11, 2017 at 12:54
@Kapil Exactly, that's what I need. For my SQL fiddle it should be "Boston|mostly cloudy" and second row "London|sunny" — Peter
– Peter, Commented Jul 11, 2017 at 12:55

Serg · Accepted Answer · 2017-07-11 12:59:27Z

3

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

answered Jul 11, 2017 at 12:59

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Over a year ago

Can you please explain the extraordinary things like top(1) with ties or order by row_number() over (partition..?

Serg Over a year ago

learn.microsoft.com/en-us/sql/t-sql/functions/… Plus, MS Sql allows row_number() in ORDER BY clause of SELECT statement

Gordon Linoff · Accepted Answer · 2017-07-12 03:27:12Z

1

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;

edited Jul 12, 2017 at 3:27

answered Jul 11, 2017 at 12:59

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

etsa Over a year ago

Gordon: I just tried to correct your answer to let it works (as I think it was correct as main idea). Obviously feel free to delete, edit, or anything you want to do with my edit.

Collectives™ on Stack Overflow

How to return only rows with maximum values

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related