Correlated Subquery in SQL

Level 4131 keystrokes

A correlated subquery uses a column of the outer query; here it finds the most expensive product in each category.

SQL code

-- Return the name, price and category of products
1SELECT
2 p.name,
3 p.price,
4 p.category
5FROM
-- p names the outer row so the subquery can refer to it
6 products p
-- Keep a product if its price equals the top price of its category
7WHERE
8 p.price = (
-- Highest price among products of the same category as p
9 SELECT MAX(price)
10 FROM products
-- p.category comes from the outer row, so this is re-evaluated for each product
-- Ties all appear; without ORDER BY, the row order may differ
11 WHERE category = p.category
12 );

Output

+--------+-------+-------------+
| name   | price | category    |
+--------+-------+-------------+
| Laptop |  1200 | Electronics |
| Jacket |   150 | Clothing    |
| Novel  |    25 | Books       |
+--------+-------+-------------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • .× 5
  • ,× 2
  • (× 2
  • )× 2
  • =× 2
  • ;× 1

More SQL examples at level 4

← SQL typing practice