CODEWITHSUNDEEP
postgresqljoinaggregation
Christian K.
Christian K.

Reputation: 3

psql, display column that is not in the group by clause

i'm having problems with a query. I have two tables: country and city and i want to display the city with the highest population per country.

Here's the query:

select country.name as coname, city.name as ciname, max(city.population) as pop 
    from city 
    join country on city.countrycode=country.code 
    group by country.name 
    order by pop;`

Error

column "city.name" must appear in the GROUP BY clause or be used in an aggregate function.

I don't know how to solve this, i tried to make a subquery but it didn't work out. How can i make it work?

Upvotes: 0

Views: 318

Answers (1)

Houari
Houari

Reputation: 5641

You can easly get it using rank function:

select * from
(
select country.name as coname, 
 city.name as ciname, 
 city.population, 
 rank() over (partition by country.name order by city.population desc) as ranking 
from 
    city 
join 
    country 
on city.countrycode=country.code 
 ) A
 where ranking = 1

Upvotes: 1

Related Questions