ORDER BY and LIMIT

Here's a fact that surprises everyone once: without ORDER BY, SQL results have no guaranteed order. Tables are sets, not lists; the engine returns rows in whatever order is convenient (insert order today, index order after tomorrow's optimization). Every query whose output order matters — which includes every graded query with more than one row — needs an explicit ORDER BY.

Sorting

SELECT name, height_m
FROM mountains
ORDER BY height_m DESC;

ASC (ascending) is the default; DESC flips it. Text sorts alphabetically, numbers numerically — and this is where honest column types (from the CREATE TABLE lesson) pay rent: heights stored as TEXT would sort '1000' before '85', alphabetically. If a numeric sort ever looks insane, check the column's type first.

Tie-breaking is the professional touch: rows equal on the first key sort arbitrarily unless you add a second:

ORDER BY height_m DESC, name ASC;

"Tallest first; equal heights alphabetically." Graders with ties in the data require the tie-breaker — and real applications do too, because arbitrary order changes between runs and someone files a bug about it. You can also sort by computed expressions or their aliases: ORDER BY population / area DESC or ORDER BY density DESC both work.

LIMIT: just the top

SELECT name, height_m
FROM mountains
ORDER BY height_m DESC
LIMIT 3;

LIMIT n cuts the result to the first n rows — after sorting, which is the entire trick: ORDER BY + LIMIT = top-N, the most common query pattern in the wild (top 10 scores, 5 most recent orders, single biggest customer). LIMIT 3 OFFSET 6 skips 6 then takes 3 — page 3 of a 3-per-page list; this is how pagination works everywhere.

The order of clauses in the statement is fixed grammar, worth pinning now:

SELECT … FROM … WHERE … ORDER BY … LIMIT …;

WHERE filters, then ORDER BY sorts what survived, then LIMIT trims. (Mental model, and also nearly the real execution order.)

Your exercise: Top 3 Tallest Mountains

Set up the data, then produce exactly the top 3 by height. The three ways this exercise is failed, in order of popularity: forgetting DESC (LIMIT then serves the three shortest — the output looks plausible and is exactly wrong); no tie-breaker when the data has equal heights; and sorting the wrong column. Say the requirement as a sentence — "by height, tallest first, take 3" — and transcribe: ORDER BY height_m DESC LIMIT 3. The sentence-to-clause translation is the skill, and it's the same one WHERE taught last lesson.