最近工作中遇到很多这样的需求,表中每个id对应很多条数据,要选出每个id对应的最新记录
表中数据类似:
id | timestamp | value |
1 | 2020-04-07 11:31:00 | 1 |
1 | 2020-04-06 12:31:00 | 2 |
2 | 2020-04-03 11:31:00 | 3 |
2 | 2020-04-07 10:31:00 | 4 |
3 | 2020-04-07 13:31:00 | 5 |
查询SQL如下:
SELECT *
FROM table_name
WHERE (id, timestamp) in (SELECT id, MAX(timestamp)
FROM table_name
GROUP BY id)
查询结果:
id | timestamp | value |
1 | 2020-04-07 11:31:00 | 1 |
2 | 2020-04-07 10:31:00 | 4 |
3 | 2020-04-07 13:31:00 | 5 |