Chapter 08 · basic · 6 min
ORDER BY & LIMIT: top-N
Rows come back from a table in no guaranteed order. When order matters (cheapest first, newest first, top 3) you have to ask for it with ORDER BY. Pair it with LIMIT and you get the single most common report in analytics: the top-N, exactly like a leaderboard.
We'll rank flights by price.
The dataset
A flights table: route, airline, price in ringgit, and departure hour (24h). One route (KUL-DPS) hasn't been priced yet, so its price is NULL, useful for seeing where NULLs land in a sort.
Schema
| id | INT |
| route | TEXT |
| airline | TEXT |
| price | NUMERIC |
| depart_hour | INT |
Example data
Sort ascending (the default)
ORDER BY price sorts from lowest to highest, ascending is the default. Cheapest flight floats to the top.
Before you run it — which route/airline do you think comes out cheapest, and where does the unpriced KUL-DPS row land?
Sort descending with DESC
Add DESC to reverse it: highest first. (ASC spells out the default, but you rarely need to write it.)
Before you run it — reversing the sort, which flight lands on top now, and where does the NULL price go?
Sort by more than one column
List several sort keys, separated by commas. The database sorts by the first; ties are broken by the second, and so on. Here flights group by route, and within each route they're ordered cheapest-first.
Before you run it — within the KUL-BKI route, which airline's row do you expect first?
Keep only the top rows with LIMIT
LIMIT n returns at most n rows. Combined with ORDER BY, this is the top-N pattern: sort the way you want, then cut.
The order of clauses is fixed: ORDER BY always comes before LIMIT.
Before you run it — same sort as the first example, just cut to 3 rows. Which 3 flights make the cut?
Why LIMIT matters beyond just tidiness
Top-N isn't just a display convenience. "Top 10 customers by spend", "3 slowest API endpoints", "cheapest flight this week" are some of the most common real questions asked of any dataset. There's also a performance angle: when a database can use an index that already matches your ORDER BY, LIMIT lets it stop as soon as it has enough rows instead of sorting the entire table first, the difference between scanning 10 rows and 10 million.
Where do NULLs sort?
The unpriced KUL-DPS route has a NULL price, and NULL isn't really "low" or "high", it's unknown. Postgres has a default rule:
| Sort direction | Default NULL position | Override |
|---|---|---|
ASC | last | NULLS FIRST |
DESC | first | NULLS LAST |
If the default isn't what you want, spell it out explicitly.
Before you run it — with NULLS FIRST added, where does KUL-DPS land compared to the default ASC sort earlier?
If you've used Excel or Google Sheets
ORDER BY is exactly Sort sheet by column, and ORDER BY ... LIMIT n is the same job as a PivotTable's "Top 10" value filter, or manually deleting all but the first few rows after a sort. SQL just does both steps as one statement, and re-runs identically every time the data changes.
Ready to practice? AirAsia's cheapest flights question below is this exact pattern.
Careful: LIMIT without ORDER BY gives you some arbitrary 3 rows, not a meaningful top 3, so always sort first. This is exactly why some questions are marked "order matters": the checker compares row order too. Filtering and sorting now covered, next we do arithmetic directly inside a query.