P03 · SQL & Querying
SQL ORDER BY and LIMIT — the top-N trap
How SQL ORDER BY and LIMIT build a top-N — and the trap where LIMIT with no ORDER BY returns arbitrary rows that silently change when the plan does.
Start with the basics
Sorting and limiting is how you produce a shortlist: order the rows, then keep the top few. ORDER BY decides the order; LIMIT keeps that many from the top.
The catch is that LIMIT on its own doesn't sort. Without an ORDER BY, 'the first 3 rows' is just whichever three the database happened to return.
It usually looks stable, because the same plan returns the same rows. The bugs show up the day the plan changes, or two rows tie at the cut-off.
One that works
| id | grams |
|---|---|
| 1 | 900 |
| 2 | 300 |
| 3 | 1500 |
SELECT id
FROM parcels
ORDER BY grams DESC
LIMIT 2;| id |
|---|
| 3 |
| 1 |
ORDER BY grams DESC makes 'the two heaviest' well-defined and stable.
LIMIT returns some rows, but only ORDER BY decides which — without it, 'the top 3' is just whatever the scan returned.
When to use it
- Reach for ORDER BY + LIMIT whenever you want a top-N or a shortlist.
- Always pair LIMIT with an ORDER BY, or the rows are arbitrary.
- Add a unique tiebreaker to the ORDER BY so ties don't wobble.
That one word — decides — is exactly what LIMIT without an ORDER BY skips. Ask for 'the top 3' with nothing to sort by, and you get three arbitrary rows that change when the plan does. Here is the day that bites.
See it happen
The top-3 that isn't — LIMIT without ORDER BY
You want the three heaviest parcels. You write LIMIT 3 but forget the ORDER BY. Watch it return three arbitrary rows that change on the next run.
| id | grams |
|---|---|
| 1 | 900 |
| 2 | 300 |
| 3 | 1500 |
| 4 | 600 |
| 5 | 1200 |
LIMIT only — arbitrary rows
SELECT id
FROM parcels
LIMIT 3;| id |
|---|
| — whatever the scan returned — |
ORDER BY then LIMIT — the real top 3
SELECT id
FROM parcels
ORDER BY grams DESC
LIMIT 3;| id |
|---|
| 3 |
| 5 |
| 1 |
The 'three heaviest parcels' shortlist is whatever the scan returned — reload the table and it silently changes.
Walk the full example in the unit →The rest of the family
More ways sorting surprises you
A plain top-N slices through tied rows at the boundary; keeping ties (or ranking) is the honest way to include the peers.
Whether missing values sort first or last depends on the database, so say it explicitly when it matters.
Ordering by a position number breaks when the SELECT list changes, and ordering by a column that isn't selected can be undefined.
Paging by offset re-runs the query per page, so rows inserted or removed between requests get skipped or shown twice.
Default string ordering follows a collation, and a binary one sorts every capital letter before every lowercase one.
Self-check
How to spot it in your own SQL
- A top-N list changes between runs with no data change.
- You wrote LIMIT without an ORDER BY.
- A top-10 cuts off some rows that tie with the tenth.
- Paginating with OFFSET skips or repeats rows.
What people miss
Ordering feels like a finishing touch, but without it 'the top rows' is whatever the scan returned — a shortlist that looks stable until the day the plan changes and it silently reorders.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why do my LIMIT results change between runs?
LIMIT with no ORDER BY returns whatever rows the scan produced, which can change when the plan or data changes.
Always pair LIMIT with an ORDER BY that fully determines the order.
from the unit →Why does my top-10 cut off rows that tie for tenth?
A plain top-N slices exactly at the row count, cutting through any rows tied at the boundary arbitrarily.
Keep ties explicitly (with a with-ties option or by ranking) when the peers should be included.
from the unit →Why does OFFSET pagination skip or repeat rows?
Offset paging re-runs the query for each page, so rows inserted or deleted between requests shift the offsets, skipping or duplicating items.
Use keyset pagination (page by the last seen key) for a stable feed.
from the unit →Do NULLs sort first or last?
It depends on the database — some sort missing values first, some last.
Say it explicitly with a nulls-first or nulls-last clause when the position matters.
from the unit →That’s the free taste