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.
You may use a SELECT statement in the FROM line.
In this example the table is
beast and the columns are
SELECT name, A.region FROM bbc AS A JOIN (SELECT region,MAX(population) AS maxpop FROM bbc GROUP BY region) AS B ON (A.region=B.region AND A.population=maxpop)
What about ties?
If one region has two countries with the same, highest population then the query will produce the right answers but there will be duplication- both countries will be shown.