Group by and Join

Question

I have a problem with using group by and join in the same query. (I use world DB in MySQL, just two tables. First - countries, second - cities). I want to get the biggest city on each continent. Here is what I tried

SELECT
    k.Continent,
    m.name,
    MAX(m.Population)
FROM
    city m
        JOIN
    country k ON m.CountryCode = k.Code
GROUP BY 1;

I get good values in population and continent column but city names are wrong. It is not the city with the biggest population but the first city on each continent from the table.

Answer 1

This is a greatest-n-per-group problem. You want to filter rather an aggregate.

You can use a correlated subquery for this:

select co.continent, ci.name, ci.population
from city ci
inner join country co where co.code = ci.countryCode
where ci.population = (
    select max(ci1.population)
    from city ci1
    inner join country co1 on co1.code = ci1.countryCode
    where co1.continent = co.continent
)

If you are lucky enough to be running MySQL 8.0, it is simpler to use window functions:

select *
from (
    select 
        co.continent, 
        ci.name, 
        ci.population, 
        rank() over(partition by co.continent order by ci.population desc) rn
    from city ci
    inner join country co where co.code = ci.countryCode
) t
where rn = 1