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

Selecting ID with Maximum Date Grouped by Category in PostgreSQL

To retrieve the ID with the maximum date for each category in PostgreSQL, we can employ the powerful DISTINCT ON clause, which is an extension to the standard DISTINCT command. This clause allows us to aggregate data based on specific criteria while preserving the uniqueness of individual rows.

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 ID with the maximum date for each category using DISTINCT ON the syntax is:

SELECT DISTINCT ON (category)
       id
FROM   tbl
ORDER  BY category, date DESC;

The resulting output will be:

id
7
2
6

This query sorts the data first by category in ascending order and then by date in descending order. The DISTINCT ON clause then ensures that for each distinct category, only the row with the maximum date is displayed, resulting in the desired output.

If the date column allows for NULL values, it is important to include the NULLS LAST clause to ensure that NULL values are sorted last. For example:

SELECT DISTINCT ON (category)
       id
FROM   tbl
ORDER  BY category, date DESC NULLS LAST;

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