如何使用PostgreSQL中的最大日期组按类别选择id ?

For an example, I would like to select id with max date group by category, the result is: 7, 2, 6

例如，我想按类别选择带有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

May I know how to do this in PostgreSQL?

我可以在PostgreSQL中这样做吗?

4 个解决方案

#1

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

这是一个独特的完美用例(Postgres特定的标准不同的扩展):

SELECT DISTINCT ON (category)
       id  -- , category, date -- add 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:

小心降序排序。如果列可以为空，则可能希望最后添加NULL:

• PostgreSQL sort by datetime asc, null first?
• 根据datetime asc进行PostgreSQL排序，先为null吗?

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

明显是最简单、最快的。相关答案的详细解释:

• Select first row in each GROUP BY group?
• 在每个组中按组选择第一行?

For big tables consider this alternative approach:

对于大型表格，请考虑以下替代方法:

• Optimize groupwise maximum query
• 优化groupwise最大查询

Performance optimization for many rows per category:

对每个类别的多行进行性能优化:

• Optimize GROUP BY query to retrieve latest record per user
• 按查询对组进行优化，以检索每个用户的最新记录

#2

Try this one:

试试这个:

SELECT * 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

#3

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

另一种方法是使用first_value窗口函数: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.

…尽管我怀疑hims056的建议在有适当索引的地方通常表现得更好。

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;

#4

SELECT id FROM tbl GROUP BY cat HAVING MAX(date)

通过具有MAX(日期)的cat从tbl组中选择id