Difference between revisions of "SELECT .. SELECT"

From SQLZOO
Jump to: navigation, search
Line 1: Line 1:
 
  <h3>The Derived Table (SELECT FROM SELECT)</h3>
 
  <h3>The Derived Table (SELECT FROM SELECT)</h3>
<p>List the largest country for each region</p>
+
<p>You can use the results from one query in another query</p>
<p>
 
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).
 
</p>
 
<p>
 
For example you can get the maximum population for each region using:
 
</p>
 
<pre>
 
SELECT region, MAX(population) AS maxpop
 
  FROM bbc
 
GROUP BY region</pre>
 
Note that this gives the population of the largest country - it does not give you the name of that
 
country.
 
<table border="1" style="" class="zoo"><tr>
 
<th>region</th>
 
<th>maxpop</th>
 
</tr>
 
<tr>
 
<td>Africa</td>
 
<td align="right">130200000</td>
 
</tr>
 
<tr>
 
<td>Americas</td>
 
<td align="right">13000000</td>
 
</tr>
 
<tr>
 
<td>Asia-Pacific</td>
 
<td align="right">1300000000</td>
 
</tr>
 
<tr>
 
<td>Europe</td>
 
<td align="right">141500000</td>
 
</tr>
 
<tr>
 
<td>Middle East</td>
 
<td align="right">74900000</td>
 
</tr>
 
<tr>
 
<td>North America</td>
 
<td align="right">295000000</td>
 
</tr>
 
<tr>
 
<td>South America</td>
 
<td align="right">182800000</td>
 
</tr>
 
<tr>
 
<td>South Asia</td>
 
<td align="right">1100000000</td>
 
</tr>
 
</table>
 
 
 
 
 
 
 
 
<div class='qu'>
 
<div class='qu'>
 
You may use a SELECT statement in the FROM line.
 
You may use a SELECT statement in the FROM line.
 
In this example the table is <code>beast</code> and the columns are <code>id</code> and <code>legs</code>.
 
In this example the table is <code>beast</code> and the columns are <code>id</code> and <code>legs</code>.
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT name, A.region
+
SELECT name, gdp_pre_capita
   FROM bbc AS A JOIN
+
   FROM
   (SELECT region,MAX(population) AS maxpop
+
   (SELECT name, gdp/population AS gdp_per_capita
     FROM bbc
+
     FROM bbc) X
  GROUP BY region) AS B
 
  ON (A.region=B.region
 
  AND A.population=maxpop)
 
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 23:34, 8 August 2012

The Derived Table (SELECT FROM SELECT)

You can use the results from one query in another query

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, gdp_pre_capita
  FROM
  (SELECT name, gdp/population AS gdp_per_capita
     FROM bbc) X

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: