SQL to get max value from each group

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, Apr 17 '17 at 10:12

score 6 · Answer 1 · answered Apr 17 '17 at 09:40

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 '17 at 09:40

Mansoor

4,061
1
17
27

This is the probably the simplest query. I would also add `ORDER BY date` if that is an additional requirement. – KartikKannapur Oct 20 '18 at 19:52
He wants to have `date` in the result which is missing in your answer. – Code Pope Nov 01 '21 at 15:39

score 2 · Answer 2 · answered Apr 17 '17 at 09:39

2

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;

answered Apr 17 '17 at 09:39

Darshan Mehta

30,102
11
68
102

score 2 · Accepted Answer · answered Apr 17 '17 at 09:41

2

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;

answered Apr 17 '17 at 09:41

Biswabid

1,378
11
26

score 2 · Answer 4 · answered Apr 17 '17 at 10:17

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

SQL to get max value from each group

4 Answers4