Practice · Agriculture · Hard

Each crop’s best year (correlated)

For each crop, return the crop and the year in which it hit its highest million_tonnes, ordered by crop.

Runs in your browser. No signup needed.

What you are given

A correlated subquery: match million_tonnes to (SELECT MAX(million_tonnes) FROM crops c2 WHERE c2.crop = c.crop). The inner query re-runs per outer row.

Starting point

SELECT crop, year
FROM crops c
WHERE million_tonnes = (
  SELECT MAX(million_tonnes) FROM crops c2 WHERE c2.crop = c.crop
)
ORDER BY crop;

The reference solution is checked automatically when you run your query on the practice page — results are compared row by row, and order matters for this one.

Did you know

A correlated subquery references the outer row — powerful, but it conceptually runs once per row, so mind the cost on big tables. (SQLite docs)

More Agriculture challenges

Which crops do we track? Easy
Total wheat on record Easy
Average output per crop Medium
Total staple output per year Medium
Above-average harvest years (CTE) Hard

Open it in the playground →