ORDER BY is the only clause on the read path that keeps a promise about arrangement: given one or more sort keys, it sequences the whole result set — and a second key sequels only where the first one ties. Master its three knobs (asc/desc, multi-key chains, NULL placement) and every report you ship ranks the way a reader expects.
What does ORDER BY guarantee — and what does nothing guarantee?
Without ORDER BY, result order is an accident of the engine's scan. With it, the arrangement is contractual. The archive's most-downloaded episodes lead with one sort key plus a cap for brevity:
SELECT title, downloads, released FROM episodes ORDER BY downloads DESC LIMIT 5;
title downloads released
----------------------------------- --------- ----------
Developing film in a hotel bathroom 41203 2025-01-15
Kitchen chemistry at altitude 35711 2025-05-13
Street photography at night 33417 2025-01-08
Mapping the shore with LiDAR 28410 2025-03-04
Composing with a single lens 25360 2025-02-05
DESC flips the default ascending direction — and on ISO-formatted dates the text ordering doubles as chronological ordering, which is why the January episode sits near the top of the downloads ranking above. Do not stretch that judgement to other date formats: day-first strings sort gibberishly, and lesson 14 treats dates as proper computable types. Also fix the pairing rule in your head: a definitive top N in any requirement means one sorted query with a cap, never a sort alone.
How do you chain sort keys for layered orders?
Multiple keys drive the layered reads: group episodes by season, and inside each season the popular ones first. The engine sorts by the first key, and only among rows tied on it does the second key rule:
SELECT season, title, downloads FROM episodes ORDER BY season, downloads DESC;
season title downloads
------ ----------------------------------- ---------
1 Reading an old survey 9124
2 Developing film in a hotel bathroom 41203
2 Street photography at night 33417
2 Composing with a single lens 25360
3 Mapping the shore with LiDAR 28410
3 The boundary of a map 19205
3 Why the horizon curves 17882
4 Kitchen chemistry at altitude 35711
4 The photograph nobody printed 21800
4 Naming wind and weather 15590
Season went ascending (default), downloads descending within each season — direction is per-key, so any combination is expressible. Two notes to build the layered habit: keys may differ in direction without apology, and the tie behaviour extends formally — equal values everywhere leave the engine no obligation, so add a tiebreaking key (usually the id) whenever stability prevents a review fight.
Where do NULLs land inside a sort?
Absent values still need a slot, and engines disagree about which end. ASC on a column with missing guests shows the archive's default: unknowns lead the pack, before every real name:
SELECT id, guest FROM episodes ORDER BY guest LIMIT 4;
id guest
-- -------------
4
7
8 Dana Kowalski
10 Joe Naylor
SQLite treats NULL as the smallest possible value, so ascending order puts it first; the two guest-less episodes take the front row. Since version 3.30, SQLite also speaks the standard fixup — an explicit NULLS LAST that moves unknowns to the finish line, keeping the human-readable names up front:
SELECT id, guest FROM episodes ORDER BY guest NULLS LAST LIMIT 4;
id guest
-- -------------
8 Dana Kowalski
10 Joe Naylor
3 Lena Marsh
5 Marcus Bell
| Engine | NULLs in ASC by default | Fixup clause |
|---|---|---|
| SQLite (3.30+) | first | NULLS LAST / NULLS FIRST |
| PostgreSQL | last | NULLS FIRST / NULLS LAST |
| MySQL older lineages | first | ORDER BY col IS NULL, col |
| SQL Server | first | ISNULL or CASE key |
The table rows are reference knowledge, not runs — nothing outside SQLite was executed here. The portable habit: never let a report's missing-values placement hinge on the engine's default; write NULLS FIRST or NULLS LAST whenever your numbers say the difference matters, and today's default-first behaviour becomes tomorrow's sorted-by-accident bug after an upgrade.
Can you sort by something you never stored?
Sort keys are expressions, not only stored columns — lesson 11 makes computed columns a first-class topic, and the sorting angle is worth a preview. Deriving a character count and sorting on it needs no schema change; the helper alias joins the ORDER BY like any other column:
SELECT title, LENGTH(title) AS chars FROM episodes ORDER BY chars DESC LIMIT 3;
title chars
----------------------------------- -----
Developing film in a hotel bathroom 35
Kitchen chemistry at altitude 29
The photograph nobody printed 29
Longest titles first, with a two-way tie at 29 characters — another chance to see why tiebreakers exist. Any expression usable in SELECT is fair game as a sort key: unit-converted figures, date parts, last name extracted with string functions. A sort alias exists only for the arrangement — do not reuse it as if the table grew a column.
Quick reference
- Ordered results come only from ORDER BY — unordered scans are ordered by luck.
- Multi-key sorts cascade: the second key sorts only within equal first keys.
- Direction is set per expression; ISO date text sorts chronologically as a by-product.
- NULL placement differs per engine; write NULLS FIRST/LAST explicitly when it matters.
Chart drill: produce a season-recap table sorted oldest to newest release within each season, list each season's least-downloaded episode, and re-sort the guest column two ways with NULLS clauses —then articulate in one sentence why the NULLS decision in a report is a product choice, not a syntax nicety.