SQL to get max value from each group [duplicate]

Question

Say I have a table

Table Plays

date | track_id | user_id | rating ----------------------------------------- 20170416 | 1 | 1 | 3 (***) 20170417 | 1 | 1 | 5 20170418 | 2 | 1 | 1 20170419 | 3 | 1 | 4 20170419 | 3 | 1 | 2 (***) 20170420 | 1 | 2 | 5

What I want to do is for each unique track_id, user_id I want the highest rating row. I.e. produces this the table below where (***) rows are removed.

20170417 | 1 | 1 | 5 20170418 | 2 | 1 | 1 20170419 | 3 | 1 | 2 20170420 | 1 | 2 | 5

Any idea what a sensible SQL query is to do this?

Why for track_id = 3 and user_id = 1 you expect rating = 2, but not 4? — Nick
– Nick, Commented Apr 17, 2017 at 10:12

Mansoor · Accepted Answer · 2017-04-17 09:40:56Z

6

Use MAX built in function along with GROUP by clause :

 SELECT track_id, user_id, MAX(rating) FROM Your_table GROUP BY track_id, user_id;

answered Apr 17, 2017 at 9:40

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

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

2 Comments

KartikKannapur Over a year ago

This is the probably the simplest query. I would also add ORDER BY date if that is an additional requirement.

Code Pope Over a year ago

He wants to have date in the result which is missing in your answer.

Darshan Mehta · Accepted Answer · 2017-04-17 09:39:43Z

Assuming you want max date as well, try the following:

SELECT track_id, user_id, MAX(rating), MAX(date) FROM plays GROUP BY track_id, user_id;

Biswabid · Accepted Answer · 2017-04-17 09:41:40Z

select * from [table] t1 inner join ( select track_id, user_id, max(rating) maxRating from [table] group by track_id, user_id ) tmp on t1.track_id = tmp.track_id and t1.user_id = tmp.user_id and t1.rating = tmp.maxRating;

Jibin Balachandran · Accepted Answer · 2017-04-17 10:17:37Z

If you have extra columns which is not mentioned in the GROUP BY clause you can go for a subquery like:

SELECT track_id,user_id,rating --, other columns you want to display FROM( SELECT track_id, user_id, rating,ROWNUMBER() OVER(PARTITION BY track_id, user_id ORDER BY rating DESC) as RN --,Other columns which you want to display FROM Your_table ) X WHERE X.RN=1

Collectives™ on Stack Overflow

SQL to get max value from each group [duplicate]

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Related