Sort and keep the top few
Order the answer with ORDER BY and cut it short with LIMIT. About 8 minutes.
ORDER BY sorts the answer
A table doesn't promise any order. If you want the rows sorted, you have to ask: ORDER BY and a column.
By default the smallest comes first (1 to 9, A to Z). Add DESC after a column to flip it, biggest first. If two rows are tied on the first column, list a second one to break the tie, and each column gets its own DESC.
ORDER BY year DESC ORDER BY genre, year DESC
LIMIT keeps the first few
"The two newest books" is two questions: sort newest first, then keep only the first two. Write ORDER BY and then LIMIT 2, and the rows past the second are cut.
OFFSET skips rows before counting. LIMIT 3 OFFSET 3 skips the first three rows and returns the next three, the second page of results.
SELECT title, year FROM books ORDER BY year DESC LIMIT 2;
| id | title | year |
|---|---|---|
| 1 | Salt Road | 1998 |
| 2 | Paper Boats | 2011 |
| 3 | Lamp Street | 1975 |
| 4 | Rain Letters | 2004 |
| 5 | Old Fort | 1962 |
ORDER BY year DESC sorts all five rows, newest year first.
A table items has name and price. What does ORDER BY price DESC, name LIMIT 1 return?
- AThe most expensive item; if two tie, the one whose name comes first
- BThe cheapest item
- CThe item whose name is first A to Z
Show the answer
The most expensive item; if two tie, the one whose name comes first. DESC puts the biggest price first, and name breaks a tie A to Z. LIMIT 1 keeps the first row.
Top three scores
The innings table has one row per innings. Show the player and runs of the three highest scores, highest first. If two scores are equal, the one made off fewer balls comes first, and if that is also equal, order by player name A to Z.
innings(id, player, runs, balls)
Up to 2,000 innings