PostgreSQL：根据排序顺序仅选择每个 id 的第一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18987650/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: Select only the first record per id based on sort order
提问by Sarah Bergquist
For the following query I need to select only the first record with the lowest shape_type value (ranges from 1 to 10). If you have any knowledge on how to easily do this is postgresql, please help. Thanks for your time.
对于以下查询，我只需要选择 shape_type 值最低（范围从 1 到 10）的第一条记录。如果您对如何轻松完成 postgresql 有任何了解，请提供帮助。谢谢你的时间。
select g.geo_id, gs.shape_type
from schema.geo g
join schema.geo_shape gs on (g.geo_id=gs.geo_id)
order by gs.shape_type asc;
回答by Roman Pekar
PostgreSQL have very nice syntax for this types of queries - distinct on:
PostgreSQL 对这种类型的查询有非常好的语法 -区别在于：
SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.
SELECT DISTINCT ON ( expression [, ...] ) 仅保留给定表达式计算结果相等的每组行的第一行。DISTINCT ON 表达式使用与 ORDER BY 相同的规则解释（见上文）。请注意，每个集合的“第一行”是不可预测的，除非使用 ORDER BY 来确保所需的行首先出现。
So your query becomes:
所以你的查询变成：
select distinct on(g.geo_id)
g.geo_id, gs.shape_type
from schema.geo g
join schema.geo_shape gs on (g.geo_id=gs.geo_id)
order by g.geo_id, gs.shape_type asc;
In general ANSI-SQL syntax for this (in any RDBMS with window functions and common table expression, which could be switched to subquery) would be:
通常，用于此的 ANSI-SQL 语法（在具有窗口函数和公共表表达式的任何 RDBMS 中，可以切换到子查询）将是：
with cte as (
select
row_number() over(partition by g.geo_id order by gs.shape_type) as rn,
g.geo_id, gs.shape_type
from schema.geo g
join schema.geo_shape gs on (g.geo_id=gs.geo_id)
)
select
geo_id, shape_type
from cte
where rn = 1
