How to Retrieve the Maximum Date ID for Each Category in PostgreSQL?

Retrieving Maximum Date ID Grouped by Category in PostgreSQL

In database operations, it's often necessary to retrieve records based on specific criteria. One such task involves selecting IDs with the maximum date for each category in a given dataset. This can be achieved effectively in PostgreSQL using the DISTINCT ON clause.

Consider the following sample data:

id  category  date
1   a         2013-01-01
2   b         2013-01-03
3   c         2013-01-02
4   a         2013-01-02
5   b         2013-01-02
6   c         2013-01-03
7   a         2013-01-03
8   b         2013-01-01
9   c         2013-01-01

To select the IDs with the maximum date for each category, you can use the following query:

SELECT DISTINCT ON (category)
       id  -- , category, date  -- any other column (expression) from the same row
FROM   tbl
ORDER  BY category, date DESC;

Explanation:

• DISTINCT ON (category): This clause ensures that duplicate categories are eliminated, enabling us to select the first unique category that satisfies the sorting criteria.
• date DESC: This descending sort order ensures that the record with the maximum date for each category is retrieved first.

The result of this query will be:

7
2
6

Note that DISTINCT ON is particularly useful when multiple records exist with the same maximum date. In such cases, it provides a way to select the first unique record for each category.

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