SQL-01-004-P · Primer — Sorting and Limiting with ORDER BY & LIMIT
The idea in one sentence
ORDER BY puts your result rows into a deliberate order — smallest-to-largest, A-to-Z, newest-first — and LIMIT then keeps only the first few of that ordered list, so together they answer "the top N" questions like the 3 highest scores or the 10 most recent orders.
Why it exists
A table has no built-in order. When you run a plain SELECT, the database is free to hand back rows in whatever sequence is cheapest for it, and that sequence can change between runs, after an update, or when the data grows — so you must never assume "the order I inserted them" or "the order I see" is guaranteed. That's fine for questions like how many customers do we have?, but the moment a human is going to read the output, order matters: a leaderboard, a list of recent sign-ups, a price list, an alphabetized directory. ORDER BY exists to make that order explicit and reproducible instead of accidental. And once rows are ordered, a second question follows almost immediately — I don't want all ten thousand of them, just the top handful — which is exactly the slice LIMIT gives you.
How it works
ORDER BY takes one or more columns and sorts the result by them, ascending by default; add DESC to flip to descending (ASC says "ascending" explicitly). When you list several columns, the database sorts by the first, and only uses the second to break ties within equal values of the first — like a phone book sorted by last name, then first name. LIMIT n then keeps only the first n rows of that sorted result, and LIMIT n OFFSET m skips the first m rows before taking n, which is how you page through results. The clauses run in a fixed pipeline: the rows are chosen and sorted first, and LIMIT slices last — so LIMIT almost never makes sense without an ORDER BY, because "the first 3 of an undefined order" is a coin flip. Here we ask for the three highest scores, breaking ties alphabetically by name:
CREATE TABLE players (name TEXT, score INTEGER);
INSERT INTO players VALUES
('Ada', 95), ('Bo', 88), ('Cy', 95), ('Dot', 72), ('El', 88);
SELECT name, score
FROM players
ORDER BY score DESC, name ASC -- highest score first; ties broken A→Z by name
LIMIT 3; -- keep only the top 3 of that ordering
-- name score
-- ---- -----
-- Ada 95 (Ada and Cy both scored 95; "Ada" < "Cy" alphabetically)
-- Cy 95
-- Bo 88The one gotcha
LIMIT without ORDER BY gives you a few rows, not the few rows you meant — and the two you get can silently change tomorrow. It's tempting to write SELECT * FROM players LIMIT 3 to "just see the top 3," but without ORDER BY there is no top; the database returns whichever three are cheapest to fetch, and that set is not stable across inserts, deletes, or even query-planner changes. The same trap bites paging: LIMIT 10 OFFSET 10 for "page 2" is only correct if page 1 used the same ORDER BY, on a column ordered consistently enough that ties don't shuffle rows between pages — if two rows tie and their relative order isn't pinned down, one of them can appear on both pages or neither. The fix is the same both times: whenever you LIMIT, give ORDER BY enough columns to make the order fully deterministic, ending on something unique like an id.
See it — how the clauses run before you get rows back
flowchart TD
A["FROM players<br/>(read all rows,<br/>no order yet)"] --> B["ORDER BY score DESC,<br/>name ASC<br/>(sort every row)"]
B --> C["LIMIT 3<br/>(keep first 3<br/>of the sorted list)"]
C --> D["Result:<br/>Ada 95, Cy 95, Bo 88"]
B -. "skip this and<br/>LIMIT is a coin flip" .-> E["ORDER BY missing<br/>= undefined order"]
The one-line takeaway
Sort first with ORDER BY (add DESC for high-to-low, extra columns to break ties), then slice with LIMIT — and never LIMIT without an ORDER BY, or "the top N" becomes "any N."
Where you'll meet it
You'll meet this in the first real query anyone asks you to write: a leaderboard's top 10, the 5 most recent orders, the cheapest products on a page, an alphabetized list of users. It's behind every "load more" button and every paginated table — that's LIMIT and OFFSET doing their job one page at a time. Interviewers lean on it constantly because it separates people who see rows from people who control them: expect "return the top 3 salaries," "get the second page of results," or the classic trap "why might this query return different rows each time it runs?" — where the answer is almost always a missing or non-deterministic ORDER BY. Get comfortable here and you've got the vocabulary for ranked, sliced, human-readable output for the rest of your SQL life.
Named concepts: ORDER BY, ASC/DESC, LIMIT and OFFSET