Difference between revisions of "SELECT .. SELECT"

From SQLZOO
Jump to: navigation, search
Line 64: Line 64:
 
     FROM bbc
 
     FROM bbc
 
   GROUP BY region) AS B
 
   GROUP BY region) AS B
   ON (A.region=B.region AND A.population=maxpop)
+
   ON (A.region=B.region  
 +
  AND A.population=maxpop)
 
</source>
 
</source>
  
Line 84: Line 85:
 
<ul>
 
<ul>
 
   <li>[http://sqlzoo.net/w/index.php/SELECT_within_SELECT_Tutorial SELECT in SELECT Tutorial]</li>
 
   <li>[http://sqlzoo.net/w/index.php/SELECT_within_SELECT_Tutorial SELECT in SELECT Tutorial]</li>
   <li>[[SELECT JOIN]]</li>
+
   <li>[[SELECT_.._JOIN |SELECT JOIN]]</li>
 
</ul>
 
</ul>

Revision as of 09:24, 17 July 2012

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 larest coutry - 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.

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)
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.

See also:
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense