Sorting & Limiting

Ask a librarian for "our newest members" and you'll get a neat little stack, newest on top, probably three or four cards. Ask a database with only the tools you have so far, and you'd get every member ever, in whatever order the drawer happened to hold them. Two clauses close that gap, and they're the friendliest ones in SQL.

ORDER BY: sort the pile

ORDER BY names the column to sort the answer by:

SELECT name, joined
FROM members
ORDER BY joined;

name   | joined
-------+-----------
Amara  | 2023-02-11
Sara   | 2024-06-30
Marcus | 2025-01-15

By default the sort runs ascending — smallest first, earliest first, A before Z. Two little words flip or confirm it: ASC (ascending, the default, rarely written) and DESC (descending — biggest, latest, Z first). Newest members first is therefore:

SELECT name, joined
FROM members
ORDER BY joined DESC;

And once more, types are the silent hero: joined is a date column, so "descending" means latest in time, not backwards-alphabetical. The sorting disasters from Unit 1 — "April 1" filing before "January 3" — were never sorting bugs. They were type bugs wearing a sorting costume.

Ties happen — two members joined the same day. You can hand the librarian a tiebreaker: ORDER BY joined DESC, name means "newest first; within a day, alphabetical." First column decides; the next one settles arguments.

LIMIT: enough is enough

Sorting made the pile ordered; it's still the whole pile. LIMIT says how much of it you actually want:

SELECT name, joined
FROM members
ORDER BY joined DESC
LIMIT 3;

Three rows, no more — the librarian counts them off the top of the sorted pile and stops. This pairing, ORDER BY plus LIMIT, is one of the most-used moves in all of SQL, because it's how every "top N" question gets asked: five most expensive products, ten most recent messages, one oldest unpaid invoice. Sort by the thing that defines "top," then take N.

The order of the clauses in your query matters and mirrors the librarian's actual sequence: find the drawer (FROM), test the cards (WHERE, if any), sort the survivors (ORDER BY), count off the top (LIMIT), copy out the columns (SELECT — said first, done last, as ever). A LIMIT without an ORDER BY is the one classic stumble here: it means "any three cards, whatever order the drawer felt like" — a shrug dressed up as an answer. If which three matters, say what "top" means first.

All four clauses compose without fuss. Three youngest students in Music:

SELECT name, age
FROM students
WHERE class = 'Music'
ORDER BY age
LIMIT 3;

Read it aloud and it's barely code at all.

So far every answer has been cards — rows, listed. But some questions don't want the cards at all. "How many members do we have?" wants a single number. Next lesson the librarian learns to count.