How to List the largerst city population for each country
-- Use country column (not country_code), order the results by the decreasing largerst city population.

enter image description here

CodePudding user response：

If you have to claim the largest city name for each country also, you can use this:

SELECT l.country, r.city, l.largest
FROM (
SELECT country, MAX(population) AS largest
FROM <tablename>
GROUP BY country
) AS l
INNER JOIN <tablename> AS r ON l.country = r.country AND l.largest = r.population
ORDER BY largest DESC
;

CodePudding user response：

SELECT MAX(population) largerst, city
FROM <tablename>
GROUP BY country
ORDER BY largerst DESC, city;