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 00:34, 9 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:
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense