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 products1SELECT2 p.name,3 p.price,4 p.category5FROM -- p names the outer row so the subquery can refer to it6 products p-- Keep a product if its price equals the top price of its category7WHERE8 p.price = ( -- Highest price among products of the same category as p9 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 differ11 WHERE category = p.category12 );Output
+--------+-------+-------------+ | name | price | category | +--------+-------+-------------+ | Laptop | 1200 | Electronics | | Jacket | 150 | Clothing | | Novel | 25 | Books | +--------+-------+-------------+ 3 rows in set
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