How to Efficiently Retrieve the Most Recent Row for Each ID in PostgreSQL?

Retrieving the Last Row for Each ID in PostgreSQL

Consider a dataset with columns for ID, date, and additional information:

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

Our goal is to retrieve the most recent row for each distinct ID.

Utilizing the Distinct On Operator

The most efficient approach in PostgreSQL is to use the DISTINCT ON operator:

SELECT DISTINCT ON (id) id, date, another_info
FROM the_table
ORDER BY id, date DESC;

This query identifies and returns only the first distinct row for each unique ID, sorted in descending order of the date column.

Using Window Functions

For a cross-database solution, we can leverage window functions:

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 creates a window function to calculate the row number for each ID, ordered by the date in descending order. The WHERE rn = 1 condition filters rows to include only the highest-ranked row for each ID, and the final ORDER BY clause ensures the results are ordered by ID.

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