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?

Answer 1

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;