How Can I Efficiently Select the ID with the Maximum Date for Each Category in PostgreSQL Using DISTINCT ON?

Using DISTINCT ON to Select Id with Maximum Date Grouped by Category in PostgreSQL

To select the id with the maximum date within each category, Postgres offers a powerful feature called DISTINCT ON. Below is the query to achieve this:

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

DISTINCT ON differentiates rows based on a specified column, and subsequent rows are filtered based on sort order. By ordering the rows in descending order of date within each category, DISTINCT ON provides the maximum date for each category.

Caution: Ensure that the date column is not NULL, as NULL values can affect the sort order. If necessary, you can add NULLS LAST to handle NULL values.

DISTINCT ON offers simplicity and efficiency. Its performance is particularly effective for smaller datasets. For larger datasets with numerous rows per category, alternative approaches may be more appropriate, such as those mentioned under the "Related Stack Overflow Questions" section.

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