Last Row In Group Based on Timestamp Column For a Subset of Group Ids – Postgres

Given a postgres table of the following form:

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

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

Advertisement

Answer