How to get country wise max populated city with population count

user_13034721 0
Viewed 405

I am currently searching for a SQL query that does the following:

I have a table of all cities worldwide with their countries and population.

e.g. the table "city" (some columns)

name country population
Berlin Germany 3640000
New York USA 8419000
Hamburg Germany 1841000
Los Angeles USA 3967000

I know need to find the city with the city with the highest population per country.

e.g. the desired result

name population country
Berlin 3640000 Germany
New York 8419000 USA

The problem is that this query:

SELECT name, MAX(population) FROM city GROUP BY country

wouldn't return the appropriate name of the city. I know why that happens but am not sure how I could solve it in another way.

Any help is appreciated! Thanks!

sql
4 Answers

ANSI SQL solution using subquery almost for any rdbms:

 create table city (name varchar(50),country    varchar(50), population int);
 insert into city values('Berlin'   ,'Germany', 3640000);
 insert into city values('New York' ,'USA', 8419000);
 insert into city values('Hamburg'  ,'Germany', 1841000);
 insert into city values('Los Angeles'  ,'USA', 3967000);

Query:

 select name, population, country from city c
 where population=(select max(population ) from city a where  a.country=c.country)

Output:

name population country
Berlin 3640000 Germany
New York 8419000 USA

db<fiddle here

user_86516010

you need to tag your dbms , but this works in most of databases including mariadb , using window function will ovoid hitting city table twice:

select * from 
  (
   select * , row_number() over (partition by country order by population desc) rn
   from city
  ) t
where rn = 1
user_13674540

you can use "NOT EXISTS"

SELECT * from country a
where not exists
(
select 1 from country b 
where a.country = b.country
and b.population > a.population
)
user_32934360 
select * from city where (country, population) in
(
select country, max(population) from city group by country
);
user_143148420
Related