如何在 PostgreSQL 中按类别选择具有最大日期组的 id？

例如，我希望按类别选择具有 max date group 的 id, 结果是: 7,2,6

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

我可以知道如何在 PostgreSQL 中做到这一点吗？

152734 次浏览
小开

Try this one:

SELECT t1.* FROM Table1 t1
JOIN
(
SELECT category, MAX(date) AS MAXDATE
FROM Table1
GROUP BY category
) t2
ON T1.category = t2.category
AND t1.date = t2.MAXDATE

See this SQLFiddle

小开

Another approach is to use the first_value window function: http://sqlfiddle.com/#!12/7a145/14

SELECT DISTINCT
first_value("id") OVER (PARTITION BY "category" ORDER BY "date" DESC)
FROM Table1
ORDER BY 1;

... though I suspect hims056's suggestion will typically perform better where appropriate indexes are present.

A third solution is:

SELECT
id
FROM (
SELECT
id,
row_number() OVER (PARTITION BY "category" ORDER BY "date" DESC) AS rownum
FROM Table1
) x
WHERE rownum = 1;
小开
最佳答案

This is a perfect use-case for DISTINCT ON - a Postgres specific extension of the standard DISTINCT:

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

Careful with descending sort order. If the column can be NULL, you may want to add NULLS LAST:

DISTINCT ON is simple and fast. Detailed explanation in this related answer:

For big tables with many rows per category consider an alternative approach:


提交答案