How to get country wise max populated city with population count

Question

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!

Answer 1

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