'For each country, find city that has the highest population and the city's population

I am using the Mondial database schema and am trying to find: For each country, find city that has the highest population and the city's population.

Right now I have:

SELECT Country.Name, city.name, MAX(city.population) Population
FROM city
Join Country
On Country.Code=City.Country
WHERE city.population IS NOT NULL
GROUP BY Country.Name, city.name
ORDER BY Country.Name;

This gives me ALL of the cities in each country and their populations and not just the largest city.

sqldatabaseoracle


Solution 1:[1]

Don't know in oracle but if done in SQL Server it can be done like this:

    Select * from
        (select 
        Country.Name,
        city.name,
        city.population,
        ROW_NUMBER() over(partition by Country.Name order by Country.Name,city.population desc) RowNum
    from Country inner join city city on Country.Code=City.Country) tbl
    where RowNum = 1

function similar to row_number in oracle will help.
Hope This help.

Solution 2:[2]

This seems to work.

It's also useful for filtering query results according to column containing an aggregate function. 

SELECT ct.name AS "Country", c1.name AS "City", c1.population AS "Population"
FROM   city c1
    JOIN country ct
    ON c1.country = ct.code
WHERE  c1.population = (SELECT max(population)
                        FROM   city c2
                        WHERE  c1.country = c2.country)
ORDER BY country

Solution 3:[3]

Here you have already done Group by Country.name so you can just have single country detail, so instead of going for the MAX(population) you can just do order by city.population also remove the group by for city.name

E.g.

SELECT Country.Name, city.name, city.population Population
FROM city
Join Country
On Country.Code=City.countrycode
WHERE city.population IS NOT NULL
GROUP BY Country.Name
ORDER BY city.population desc;

This will not give you the countries in sorted order but that can also be done after adding another order by on top of it if you really want country name also sorted.

SELECT Country.Name, city.name, city.population Population
FROM city
Join Country
On Country.Code=City.countrycode
WHERE city.population IS NOT NULL
GROUP BY Country.Name
ORDER BY country.name, city.population desc;

Hope that helps to simplify the SQL query. This I have tested in MySQL.

Solution 4:[4]

You cannot use MAX in multiple select try this:

SELECT Country.Name, city.name, city.population
FROM city
Join Country
On Country.Code=City.Country
WHERE city.population IS NOT NULL and city.population in (SELECT MAX(population) FROM city limit 1)
GROUP BY Country.Name, city.name
ORDER BY Country.Name;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Kai
Solution 2 daintym0sh
Solution 3 Elikill58
Solution 4

Related Questions