Given a postgres table of the following form:
x
group id | timestamp | value
---------+-------------------------------+------
1 | 2020-04-15 15:04:44.020288+00 | 8.0
2 | 2020-04-15 15:05:44.020288+00 | 9.0
3 | 2020-04-15 15:06:44.020288+00 | 10.0
4 | 2020-04-15 15:07:44.020288+00 | 11.0
1 | 2020-04-15 15:08:44.020288+00 | 12.0
2 | 2020-04-15 15:09:44.020288+00 | 13.0
3 | 2020-04-15 15:10:44.020288+00 | 14.0
4 | 2020-04-15 15:11:44.020288+00 | 15.0
What is the SQL query to retrieve the last row for a subset of group ids based on the timestamp column?
For example, retrieving the last row for group ids {1,3}
to produce:
group id | timestamp | value
---------+-------------------------------+------
1 | 2020-04-15 15:08:44.020288+00 | 12.0
3 | 2020-04-15 15:10:44.020288+00 | 14.0
Thank you in advance for your consideration and response
Advertisement
Answer
A simple and efficient method to solve this greatest-n-per-group problem in Postgres is to use distinct on
:
select distinct on (group_id) t.*
from mytable t
where group_id in (1, 3)
order by group_id, timestamp desc