Extracting the Last Row for Each ID in Postgresql

Consider a dataset with columns named id, date, and another_info. The objective is to extract the last information (row) for each unique id.

To accomplish this in Postgresql, two methods are commonly used:

Distinct On Operator

Postgresql provides the distinct on operator, which allows you to specify that only distinct values of a particular column(s) be returned. In this case, the operator can be used as follows:

select distinct on (id) id, date, another_info
from the_table
order by id, date desc;

This query ensures that only the last instance of each unique id is returned, ordered in descending order by date.

Window Functions

Alternatively, you can use window functions to achieve the same result. A window function allows you to perform calculations or aggregations on a partitioned set of rows. In this case, the following query can be used:

select id, date, another_info
from (
  select id, date, another_info, 
         row_number() over (partition by id order by date desc) as rn
  from the_table
) t
where rn = 1
order by id;

This query uses the row_number() function to assign a sequential number to each row within each id partition. The where clause filters the results to include only the rows with the highest rn value (i.e., the last row for each id).