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

PostgreSQL: Retrieving the Last Row for Each ID

Problem:

Given the following data, you want to extract the last record for each unique ID:

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

Desired Result:

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

Solution:

There are two efficient methods to achieve this in PostgreSQL:

Method 1: Using the DISTINCT ON Operator

Postgres provides the DISTINCT ON operator, which enables you to select distinct rows based on one or more columns and then apply other operations. For this scenario, you can use:

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

Method 2: Using a Window Function

Window functions allow you to perform aggregations or calculations over a set of rows that can be defined dynamically. You can use a window function called ROW_NUMBER() to assign a rank to each row within each ID group and select the row with the highest rank:

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;

The solution using a window function is typically faster than using a subquery.

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