问题描述
例如,我想按类别选择具有最大日期组的 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?
推荐答案
这是 DISTINCT ON
- 标准 DISTINCT
的 Postgres 特定扩展:
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;
小心降序排序.如果该列可以为 NULL,您可能需要添加 NULLS LAST
:
Careful with descending sort order. If the column can be NULL, you may want to add NULLS LAST
:
DISTINCT ON
简单快速.此相关答案中的详细说明:
DISTINCT ON
is simple and fast. Detailed explanation in this related answer:
对于每个 category
有很多行的大表,考虑另一种方法:
For big tables with many rows per category
consider an alternative approach:
这篇关于如何在PostgreSQL中按类别选择具有最大日期组的ID?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!