Sorting and Limiting

Here is an uncomfortable truth about every result you've seen so far: the rows came back in whatever order the engine found convenient — which is to say, in no promised order at all. The films happened to appear in id order because the table is small and young. Nothing guarantees they will tomorrow.

This page adds the two clauses that turn "some rows" into a proper answer. ORDER BY imposes the order you actually mean, and LIMIT keeps only the top of it. Between them they answer half of Lora's daily questions — longest films first, the five newest customers — and they teach a rule worth engraving: "top five" means nothing until something defines top.

No ORDER BY, No Order

Chapter 2 promised that tables have no built-in row order, and this is that promise seen from the query side. Without an ORDER BY, the database is free to return rows in any sequence it likes: whatever it found first on disk, whatever a recent change shuffled around. If the same query has returned the same order a hundred times, that is luck, not contract — and luck runs out precisely when the table grows big enough for you to care.

So treat it as a habit rather than an option: any result a human will read in order gets an ORDER BY. It is one line, and it converts a coincidence into a guarantee.

ORDER BY: Ascending, Descending, Tie-Breaking

Lora wants the schedule board to show the longest films first. In English: show each title and runtime, from the films table, ordered by runtime, biggest first. The keyword DESC — descending — says "biggest first"; its mirror ASC, ascending, is the default and rarely written out:

SELECT title, runtime_min
FROM films
ORDER BY runtime_min DESC;

You can sort by more than one column, and the second column is not a second sort — it is a tie-breaker. ORDER BY rating, title reads out loud as: by rating first, and within each rating, alphabetically by title. The title ordering only ever applies among rows whose ratings are equal; it never reshuffles the rating order. Multi-column sorting is a sequence of tie-breaks, not a blend.

LIMIT Is a Knife, Not a Filter

The Marquee now has 212 customers, and Lora wants to greet the newest ones by name. She needs the five most recent joiners — which is a sort plus a cut. First order everyone by joining date, newest first; then LIMIT 5 slices off the top five rows and discards the rest:

SELECT name, joined_on
FROM customers
ORDER BY joined_on DESC
LIMIT 5;

The order of operations is the whole lesson. LIMIT is a knife that cuts the result after sorting — it doesn't search for the newest anything. Run LIMIT 5 without an ORDER BY and you get five rows, certainly, but they are merely some five rows, in the engine's convenience order. "Top 5" only means something because ORDER BY defined top first. Think of race results: the finishing order exists only after the race is scored, and "the top three" without scoring is just three runners standing around. Same here — sort defines top, then the knife cuts. That's the picture; back to the clauses.

One honest engine note: LIMIT is the word in PostgreSQL, MySQL, and SQLite, but SQL Server spells the same idea SELECT TOP 5 …, and the SQL standard's official spelling is a FETCH FIRST 5 ROWS ONLY clause — same knife, three labels.

Where Do NULLs Sort?

One loose end from the missing-data story. Suppose some customers have no joining date on file — where do those NULL rows land in a sorted result? The honest answer: engines disagree; some put NULLs first, some last, and the default can flip between ascending and descending sorts. Because unknown has no natural place in an ordering, the standard lets you say what you mean explicitly: ORDER BY joined_on DESC NULLS LAST pins the unknowns to the bottom, and NULLS FIRST does the opposite. You won't need it often — but the first time a report puts a row of blanks proudly at the top, you'll know both why it happened and the two words that fix it.