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

Select the Maximum Date ID for Each Category in PostgreSQL

In a database containing a table with data regarding categories, dates, and IDs, it is possible to retrieve the ID with the most recent date for each category. 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, PostgreSQL provides the DISTINCT ON clause. This clause allows you to group and compare rows based on specified columns while selecting distinct values.

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

The query prioritizes the sorting of rows by ascending category order and descending date order. As a result, for each unique category, the last row, which has the maximum date, is returned.

id  category  date
7   a         2013-01-03
2   b         2013-01-03
6   c         2013-01-03

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