如何在PostgreSQL中按类别选择具有最大日期分组的ID? [英] How to select id with max date group by category in PostgreSQL?

问题描述

例如，我想按类别选择最大日期分组的ID， 结果是:7，2，6

For an example, I would like to select id with max date group by category, the result is: 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中做到这一点吗?

May I know how to do this in PostgreSQL?

推荐答案

这是

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

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

小心降序排列.如果该列可以为NULL，则可能要添加NULLS LAST:

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

• PostgreSQL按日期时间asc排序，是否为null?

DISTINCT ON最简单，最快.此相关答案中的详细说明:

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

• 在每个GROUP BY组中选择第一行?

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

For big tables consider this alternative approach:

• 优化分组最大查询

每个category的许多行的性能优化:

• 将GROUP BY查询优化为检索每个用户的最新记录

这篇关于如何在PostgreSQL中按类别选择具有最大日期分组的ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！