sql - PostgreSQL select max with group by and additional value

Thursday, 2 February 2017

With the following data from a SELECT * FROM (SELECT...) AS foo:

ID    Country   Area
1     US        100

1     UK        200
2     AU        150
2     NZ        250

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

The the result of the query would be:

1     UK     200

2     NZ     250

Answer

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:

Blog