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

Selecting ID with Maximum Date Grouped by Category in PostgreSQL

To determine the id with the maximum date for each category within a table, PostgreSQL offers a suitable approach. Let's consider the 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 achieve the desired result, we can utilize PostgreSQL's DISTINCT ON clause. This clause allows us to select unique rows based on a specified expression, grouping the results by another column. In this case, we will group the rows by category and select the distinct id with the maximum date within each group.

Here's the query to accomplish this:

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

The DISTINCT ON clause ensures that only the first occurrence of each distinct category value is selected. The ORDER BY clause sorts the results first by category in ascending order (alphabetical order) and then by date in descending order. This ordering ensures that the row with the latest date for each category appears first.

Executing this query will yield the following result:

id

7

2

6

These IDs (7, 2, 6) represent the maximum dates for categories a, b, and c, respectively.

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