SELECT .. SELECT

The Derived Table (SELECT FROM SELECT)

List the largest country for each region

Sometimes you need to use the results from one query in another.
To show the largest country (by population) for each region you can join the list of countries
to the list of regions (with max populations).

For example you can get the maximum population for each region using:

SELECT region, MAX(population) AS maxpop
FROM bbc
GROUP BY region

Note that this gives the population of the largest country - it does not give you the name of that
country.

region

maxpop

Africa

130200000

Americas

13000000

Asia-Pacific

1300000000

Europe

141500000

Middle East

74900000

North America

295000000

South America

182800000

South Asia

1100000000

You may use a SELECT statement in the FROM line.
In this example the table is beast and the columns are id and legs.