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.
CodePudding user response：
SELECT a.* FROM scores as a
LEFT JOIN scores as b ON (a.teamid = b.teamid AND a.tmstamp < b.tmstamp)
WHERE b.tmstamp is NULL;
Essentially you join the table to itself based upon the team id but ensuring that the timestamp is the greater
CodePudding user response：
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;