Search code examples
mysqlgroup-byconditional-statementsaggregate

SQL: how to pick a value based on other column in an aggregated column

If I have a table like this:

team_id score timestamp
1 8 2022-01-05
1 10 2022-02-01
2 5 2022-01-06
2 9 2022-01-15
2 7 2022-01-20

and I only want the team ID and the latest score grouped by the ID:

team_id score
1 10
2 7

So the questions is when I group by the ID, how do I select the right score based on the timestamp? can I do this in one query? thanks.

Solution

  • You can use the max window function:

    select team_id, score from
(select *, max(timestamp) over(partition by team_id) as maxt from table_name) t
where timestamp = maxt;

    Fiddle