The ORDER BY is the only clause on the read path that keeps a promise about the arrangement. Given one or more sort keys, it sequences the whole result set, and a second key applies only where the first one ties. Master its three knobs, which are the ascending and descending direction, the multi-key chains and the NULL placement, and every report that you ship will rank the way that a reader expects.
This is the first lesson in the Sorting, Functions and Shaping module, and the next lesson in this module is about "How Do Expressions and CASE Transform Columns?".
What does the ORDER BY guarantee, and what does nothing guarantee?
Without an ORDER BY, the order of the result is an accident of the scan that the engine happened to perform. With it, the arrangement is contractual.
The archive's most-downloaded episodes lead with one sort key plus a cap for the 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-05The DESC flips the default ascending direction. On the ISO-formatted dates, the text ordering doubles as the chronological ordering, which is why the January episode sits near the top of the downloads ranking above. Do not stretch that judgement to the other date formats. The day-first strings sort gibberishly, and lesson 14 treats the dates as the 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, and never a sort on its own.
How do you chain the sort keys for the layered orders?
The multiple keys drive the layered reads. You group the episodes by the season, and inside each season the popular ones come first. The engine sorts by the first key, and only among the rows that are 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 15590The season went ascending, which is the default, and the downloads went descending inside each season. The direction is set per key, so any combination is expressible.
There are two notes to build the layered habit. The keys may differ in their direction without any apology. And the tie behaviour extends formally. When the values are equal everywhere, the engine has no obligation, so you add a tie-breaking key, which is usually the id, whenever the stability prevents a review fight.
Where do the NULLs land inside a sort?
The absent values still need a slot, and the engines disagree about which end that slot is on.
An ASC on a column with the missing guests shows the archive's default. The 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 NaylorSQLite treats a NULL as the smallest possible value, so the ascending order puts it first, and the two guest-less episodes take the front row. Since the version 3.30, SQLite also speaks the standard fixup, which is an explicit NULLS LAST that moves the unknowns to the finish line and keeps 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 rows in that table are the reference knowledge and not the runs. Nothing outside SQLite was executed here.
The portable habit is this one. You never let the placement of the missing values in a report hinge on the default of the engine. You write the NULLS FIRST or the NULLS LAST whenever your numbers say that the difference matters. Today's default-first behaviour becomes tomorrow's sorted-by-accident bug after an upgrade.
Can you sort by something that you never stored?
Yes. The sort keys are expressions and not only the stored columns. Lesson 11 makes the computed columns a first-class topic, and the sorting angle is worth a preview here.
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 29The longest titles come first, with a two-way tie at 29 characters. That is another chance to see why the tiebreakers exist.
Any expression that is usable in the SELECT is fair game as a sort key. The unit-converted figures, the date parts, and the last name that is extracted with the string functions all work.
A sort alias exists only for the arrangement, so you do not reuse it as if the table had grown a column.
Quick reference
- The ordered results come only from the ORDER BY. The unordered scans are ordered by luck.
- The multi-key sorts cascade. The second key sorts only within the equal first keys.
- The direction is set per expression, and the ISO date text sorts chronologically as a by-product.
- The NULL placement differs per engine, so you write the NULLS FIRST or the NULLS LAST explicitly when it matters.
Chart drill: produce a season-recap table that is sorted from the oldest to the newest release inside each season, list the least-downloaded episode of each season, and re-sort the guest column in two ways with the NULLS clauses. Then you articulate in one sentence why the NULLS decision in a report is a product choice and not a syntax nicety.