How to Efficiently Retrieve the Last Row for Each Unique Identifier in PostgreSQL?

Postgresql: Extracting the Last Row for Each Unique Identifier

In PostgreSQL, you may encounter situations where you need to extract the information from the last row associated with each distinct identifier within a dataset. Consider the following data:

  id    date          another_info<br>  1     2014-02-01         kjkj<br>  1     2014-03-11         ajskj<br>  1     2014-05-13         kgfd<br>  2     2014-02-01         SADA<br>  3     2014-02-01         sfdg<br>  3     2014-06-12         fdsA<br>

To retrieve the last row of information for each unique id in the dataset, you can employ Postgres' efficient distinct on operator:

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

This query will return the following output:

  id    date          another_info<br>  1     2014-05-13         kgfd<br>  2     2014-02-01         SADA<br>  3     2014-06-12         fdsA<br>

If you prefer a cross-database solution that may sacrifice slight performance, you can use a window function:

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;

In most cases, the solution involving a window function is faster than using a sub-query.

The above is the detailed content of How to Efficiently Retrieve the Last Row for Each Unique Identifier in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!