This one I've been spending the last hours on. Still haven't found an answer so I figured I'd ask. I have two tables:
Country - Code, Name, Populaion.
City - CountryCode, Name, Population.
The Foreign key is the CountryCode which response to country.Code.
I'm trying to find the most populated city in each country, where the output is the name of the country and the city. I know this could be done with Max(), but I'm having a struggle limiting my table to showing all the countries name and showing onle the name of the most populated city.
SELECT country.name, city.name, MAX(city.Population)
FROM city
LEFT JOIN country
ON city.CountryCode=Country.Code
GROUP BY city.name, country.name, city.population
ORDER BY city.population DESC;
This only gives me all the countries and all the cities. Could anyone help me narrow it down so it only shows every countries name but with their largest city?
You want to first the max population in each country and then join it with the city table to know which city it belongs to. After that, join it with the country table to get the required result.
select co.name,
ct.name,
ct.population
from (
select c1.*
from city c1
join (
select countryCode,
max(population) population
from city
group by countryCode
) c2 on c1.countryCode = c2.countryCode
and c1.population = c2.population
) ct
join country co on ct.countryCode = co.code;
Another way of finding max in group using left join:
select co.name,
ct.name,
ct.population
from (
select c1.*
from city c1
left join city c2 on c1.countryCode = c2.countryCode
and c1.population < c2.population
where c2.countryCode is null
) ct
join country co on ct.countryCode = co.code;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments