PostgreSQL select max with group by and additional value [duplicate]

Question

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 1

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:

• Select first row in each GROUP BY group?

Answer 2

Try this

select ID,Country,Area
from (SELECT...) AS foo
WHERE Area = (SELECT MAX(Area)
              FROM (SELECT...) AS foo2
              WHERE foo.ID = foo2.ID )