MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.36


CREATE TABLE cities 
(
    country VARCHAR(20),
    city VARCHAR(20),
    population INT,

    CONSTRAINT cities_pk      PRIMARY KEY (country, city),
    KEY cities_city_ix (city, population)
  
);
INSERT INTO cities VALUES 
('Italy', 'Milano', 1000000), ('Italy', 'Rome', 2000000), ('Italy', 'Bologna', 800000), 
('Poland', 'Warszawa', 1000000), ('Poland', 'Wroclaw', 700000);

Records: 5  Duplicates: 0  Warnings: 0


SELECT
  country, city, population, 
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY country, population DESC) AS rn
FROM 
  cities;


country
city
population
rn


Italy
Rome
2000000
1

Italy
Milano
1000000
2

Italy
Bologna
800000
3

Poland
Warszawa
1000000
1

Poland
Wroclaw
700000
2


SELECT
  country, city, population
FROM
(
  SELECT
    country, city, population, 
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY country, population DESC) AS rn
  FROM 
    cities
) AS pops
WHERE rn = 1
ORDER BY country;


country
city
population


Italy
Rome
2000000

Poland
Warszawa
1000000


EXPLAIN FORMAT=TREE
SELECT
  country, city, population
FROM
(
  SELECT
    country, city, population, 
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY country, population DESC) AS rn
  FROM 
    cities
) AS pops
WHERE rn = 1
ORDER BY country;


EXPLAIN


-> Sort: pops.country  (cost=0.45..0.45 rows=0)
    -> Index lookup on pops using <auto_key0> (rn=1)  (cost=0.35..0.35 rows=1)
        -> Materialize  (cost=0..0 rows=0)
            -> Window aggregate: row_number() OVER (PARTITION BY cities.country ORDER BY cities.country,cities.population desc ) 
                -> Sort: cities.country, cities.country, cities.population DESC  (cost=0.75 rows=5)
                    -> Index scan on cities using cities_city_ix  (cost=0.75 rows=5)

country	city	population	rn
Italy	Rome	2000000	1
Italy	Milano	1000000	2
Italy	Bologna	800000	3
Poland	Warszawa	1000000	1
Poland	Wroclaw	700000	2