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.
Sort ascending (the default)
ORDER BY price sorts from lowest to highest, ascending is the default. Cheapest flight floats to the top:
| route | airline | price |
|---|---|---|
| KUL-PEN | AirAsia | 59.00 |
| KUL-PEN | Batik Air | 75.00 |
| KUL-SIN | AirAsia | 89.00 |
| KUL-BKI | AirAsia | 120.00 |
| KUL-SIN | Malaysia Air | 145.00 |
| KUL-BKI | Malaysia Air | 199.00 |
| KUL-DPS | Batik Air | NULL |
Cheapest first
Sort descending with DESC
Add DESC to reverse it: highest first. (ASC spells out the default, but you rarely need to write it.)
Most expensive first
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.
By route, then price
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:
| route | airline | price |
|---|---|---|
| KUL-PEN | AirAsia | 59.00 |
| KUL-PEN | Batik Air | 75.00 |
| KUL-SIN | AirAsia | 89.00 |
The order of clauses is fixed: ORDER BY always comes before LIMIT.
The 3 cheapest flights
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.
Forcing the unpriced route to the top
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.