Conditional selection in a GROUP BY clause

Question

I am an SQL beginner and ran into the following problem. Consider a table with data on countries, cities within that country and their population:

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

INSERT INTO cities VALUES
   ('Italy', 'Milano', 1000000), 
   ('Italy', 'Rome', 2000000), 
   ('Italy', 'Bologna', 800000), 
   ('Poland', 'Warszawa', 1000000), 
   ('Poland', 'Wroclaw', 700000);

I would like to write a query which returns the country name, alongside the population of its biggest city and the city name itself. The first two fields are straightforward using GROUP BY. However, I don't know how to include the name of the biggest city. I tried:

SELECT 
    country, MAX(population), city
FROM
    cities
WHERE
    population = (SELECT 
            MAX(population)
        FROM
            cities
        )
GROUP BY country, city;

but this selects only one record (the one about Rome). I'd like the query to return one record per country group, i.e. in this case Rome and Warszawa. The WHERE condition cannot be:

population = (SELECT 
            MAX(population)
        FROM
            cities
        GROUP BY country)

because then the subquery returns two rows, leading to Error 1242 in the main query.

Answer 1

A simple way of doing this is to use the ROW_NUMBER() (manual index and function descriptions) window function - no need to use a JOIN. All of the code below is available on the fiddle here:

I set up the table (see below - added two indexes) and then populated it with your data (see fiddle).

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

    CONSTRAINT cities_pk      PRIMARY KEY (country, city),
    KEY cities_city_ix (city, population)
  
);

Then, first thing is to run (this is by way of explanation):

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

Result:

country  city     population  rn
  Italy  Rome        2000000   1  <--- Italy's biggest city
  Italy  Milano      1000000   2
  Italy  Bologna      800000   3
 Poland  Warszawa    1000000   1  <--- Poland's   "      "
 Poland  Wroclaw      700000   2

So, now run this SQL picking out the top cities from the query above as the "pops" subquery (or derived table):

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;

Result:

country  city      population
Italy    Rome         2000000
Poland   Warszawa     1000000

Window functions are very powerful and will repay any effort you put into learning them many times over. ROW_NUMBER() is more powerful than FIRST_VALUE() or LAST_VALUE() because it allows you to choose not only the top 1, but the top 2, 3... &c. Again, read & practice a bit until you've grokked them!

Just as an aside, I would append these lines to the bottom of the table definition - PK and an INDEX (or KEY - less typing :-) ).

CONSTRAINT cities_pk      PRIMARY KEY (country, city),
KEY cities_city_ix (city, population)

These appear to produce a nice plan - all indexes, no table scans! See fiddle.

EXPLAIN
-> Sort: pops.country  (cost=0.00..0.00 rows=0)
    -> Index lookup on pops using <auto_key0> (rn=1)
        -> Materialize  (cost=0.00..0.00 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

Caveat: The EXPLAIN was done on a minimal dataset - I would advise you to test all of the answers here on your hardware and your data so that you utilise the optimum solution.

Answer 2

If your table has the data distribution similar to what a real countries/cities table would have (~200 countries, hundreds of thousands of cities), you can use a lateral query and improve its performance by creating an index on (country, population).

It would benefit from a skip scan on country, and a lateral index scan on the index in a nested loop.

INSERT
INTO    cities (coutnry, city, population)
WITH    RECURSIVE q AS
        (
        SELECT  1 AS value
        UNION ALL
        SELECT  value + 1
        FROM    q
        WHERE   value < 1000000
        )
SELECT  CONCAT('country', value % 200),
        CONCAT('city', value),
        FLOOR(RAND() * 10000000)
FROM    q;

CREATE INDEX ix_cities_country_population ON cities (country, population);

ANALYZE TABLE cities;

, and the query:

SELECT   first.*
FROM     (SELECT DISTINCT country FROM cities) countries,
         LATERAL
         (
         SELECT  *
         FROM    cities
         WHERE   cities.country = countries.country
         ORDER BY
                 country DESC, population DESC
         LIMIT 1
         ) first
ORDER BY
         country;

The query completes in 20 ms on my VM.

Here's its plan:

-> Sort: `first`.country  (actual time=8.19..8.2 rows=200 loops=1)
    -> Stream results  (cost=3404 rows=202) (actual time=4.1..7.91 rows=200 loops=1)
        -> Nested loop inner join  (cost=3404 rows=202) (actual time=4.1..7.83 rows=200 loops=1)
            -> Invalidate materialized tables (row from countries)  (cost=160 rows=202) (actual time=4.01..4.07 rows=200 loops=1)
                -> Table scan on countries  (cost=155..160 rows=202) (actual time=4..4.05 rows=200 loops=1)
                    -> Materialize  (cost=155..155 rows=202) (actual time=4..4 rows=200 loops=1)
                        -> Covering index skip scan for deduplication on cities using ix_cities_country_population  (cost=135 rows=202) (actual time=0.0953..3.73 rows=200 loops=1)
            -> Table scan on first  (cost=2717..2717 rows=1) (actual time=0.0184..0.0185 rows=1 loops=200)
                -> Materialize (invalidate on row from countries)  (cost=2714..2714 rows=1) (actual time=0.018..0.018 rows=1 loops=200)
                    -> Limit: 1 row(s)  (cost=2714 rows=1) (actual time=0.0168..0.0168 rows=1 loops=200)
                        -> Index lookup on cities using ix_cities_country_population (country=countries.country) (reverse)  (cost=2714 rows=4956) (actual time=0.0165..0.0165 rows=1 loops=200)

Answer 3

You need to join the country and population as follows

SELECT A.country,A.city,A.pop population FROM
(SELECT country,city,population pop FROM cities) A INNER JOIN
(SELECT country,MAX(population) pop FROM cities GROUP BY country) B 
USING (country,pop);

Subquery A is just all rows in city table

Subquery B is all countries and its MAX city population

UPDATE 2023-10-03 14:21 EDT

Here is your sample data and my query in a script

root@redwards-8CG8490YXM:~/DBASE# cat dzejkob.sql
DROP DATABASE IF EXISTS dzejkob;
CREATE DATABASE dzejkob;
USE dzejkob

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

INSERT INTO cities VALUES
('Italy', 'Milano', 1000000),
('Italy', 'Rome', 2000000),
('Italy', 'Bologna', 800000),
('Poland', 'Warszawa', 1000000),
('Poland', 'Wroclaw', 700000);

SHOW CREATE TABLE cities\G
SELECT * FROM cities;

SELECT A.country,A.city,A.pop population FROM
(SELECT country,city,population pop FROM cities) A INNER JOIN
(SELECT country,MAX(population) pop FROM cities GROUP BY country) B
USING (country,pop);
root@redwards-8CG8490YXM:~/DBASE#

Here is the script executed

root@redwards-8CG8490YXM:~/DBASE# mysql --table < dzejkob.sql
*************************** 1. row ***************************
       Table: cities
Create Table: CREATE TABLE `cities` (
  `country` varchar(20) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `population` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
+---------+----------+------------+
| country | city     | population |
+---------+----------+------------+
| Italy   | Milano   |    1000000 |
| Italy   | Rome     |    2000000 |
| Italy   | Bologna  |     800000 |
| Poland  | Warszawa |    1000000 |
| Poland  | Wroclaw  |     700000 |
+---------+----------+------------+
+---------+----------+------------+
| country | city     | population |
+---------+----------+------------+
| Italy   | Rome     |    2000000 |
| Poland  | Warszawa |    1000000 |
+---------+----------+------------+
root@redwards-8CG8490YXM:~/DBASE#

UPDATE 2023-10-04 10:56 EDT

As pointed out to me by ypercube, here is an update to the query

SELECT A.country,A.city,A.population
FROM cities A INNER JOIN
(SELECT country,MAX(population) pop FROM cities GROUP BY country) B
ON A.country=B.country AND A.population=B.pop;

NOTE : Vérace's answer is a way better solution