PostgreSQL选择最大，分组依据和附加值 [英] PostgreSQL select max with group by and additional value

问题描述

具有以下数据from a SELECT * FROM (SELECT...) AS foo:

ID    Country   Area
1     US        100
1     UK        200
2     AU        150
2     NZ        250

如何按ID选择顶部区域和国家/地区?因此GROUP BY ID和MAX(DESC)但还包括County.

how can you select the top area and country by ID? So GROUP BY ID and MAX(DESC) but also include the County.

查询结果将是:

1     UK     200
2     NZ     250

推荐答案

SELECT DISTINCT ON (ID)
       ID, Country, Area
FROM   foo
ORDER  BY ID, Area DESC NULLS LAST;

详细说明和特殊情况下更快替代品的链接:

Detailed explanation and links to faster alternatives for special cases:

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

这篇关于PostgreSQL选择最大，分组依据和附加值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！