PostgreSQL select max with group by and additional value

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

score 11 · Accepted Answer · edited May 23 '17 at 10:27

11

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?

edited May 23 '17 at 10:27

Community

1
1

answered May 20 '15 at 14:25

Erwin Brandstetter

605,456
145
1,078
1,228

score 2 · Answer 2 · answered May 20 '15 at 14:24

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 )

answered May 20 '15 at 14:24

Robert

25,425
8
67
81

Thanks for the reply. Erwin Brandstetters solution made it possible to do it without having to run the (SELECT.. twice). – Matt May 20 '15 at 14:28
@Matt true his answer is better! – Robert May 20 '15 at 14:28

PostgreSQL select max with group by and additional value

2 Answers2

Linked