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

Retrieving Last Row for Each ID in PostgreSQL

When working with data, it's often necessary to extract specific information from various datasets. In the case of PostgreSQL, users may encounter scenarios where they need to obtain the latest record for each unique ID.

Scenario

Consider the following table:

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

Objective

The goal is to extract the last row of data for each unique ID in a new table:

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

Solution 1: Using Postgres' DISTINCT ON Operator

Postgres' DISTINCT ON operator efficiently handles this scenario:

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

Solution 2: Using a Window Function

For cross-database compatibility, window functions like row_number() can be utilized:

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;

Benchmarks typically indicate that the window function approach is faster than sub-queries.

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