Sorting and taking the top

Order decides

A query without an ORDER BY returns rows in whatever order the database finds convenient, and that order is allowed to change between runs. ORDER BY year sorts ascending — oldest first — and ORDER BY year DESC reverses it.

Sort by more than one key by listing them: ORDER BY author ASC, year DESC sorts by author, and breaks ties with the newest first. Text sorts alphabetically, numbers numerically, and each column can have its own direction.

Taking the top

LIMIT 3 keeps the first three rows of the sorted result, and LIMIT 3 OFFSET 3 skips three and takes the next three — that is how a page of results is built. OFFSET on its own is rarely useful; it is the second half of a page, not a place to start.

The order of operations matters: LIMIT throws rows away, so it must come after ORDER BY in intent as well as in the query. Limiting an unsorted result is asking for an arbitrary three rows.

Ties at the boundary are the quiet version of the same problem. If the third and fourth rows share a year, LIMIT 3 keeps whichever the database happened to reach first, and that choice can differ between runs. The tie-breaker in the next section is what makes the boundary mean something.

Where sorting happens in the query

Sorting is the step after filtering and before limiting. The database keeps the rows that pass WHERE, orders that smaller set, and only then applies LIMIT. This is why a filtered, sorted query is cheaper than sorting the whole table and throwing most of it away.

Unlike WHERE, ORDER BY can use an alias from the SELECT list because the columns already exist by the time it runs. It can also sort by an expression, such as ORDER BY length(title), though the column name is usually easier to read.

Sorting is work the database has to do

A sort is not free. If an index already holds the rows in the order you asked for, the database can read it and be done; if not, it has to compare rows and build the order itself, in memory while the set is small and in a temporary file once it is not.

EXPLAIN QUERY PLAN is how you find out which happened. It prints the steps the database intends to take, and a line about a temporary b-tree is the sign that the sort is being built from scratch. That is the first thing to look at when a query that used to be instant becomes slow, long before anyone reaches for a rewrite.

Text, numbers and dates sort differently

ORDER BY compares values by their type. Numbers sort numerically, so 9 comes before 10. Text sorts by its characters, which is why the strings '10' and '9' sort the other way round: the first character decides, and '1' is less than '9'.

The same reasoning is the argument for storing dates as YYYY-MM-DD text and quantities as numbers — the stored form and the sort order then agree, and no conversion is needed to compare them. Text also sorts by collation, so case and accents can order differently from one database to the next.

Newest first, three at a time

SELECT title, year
FROM books
ORDER BY year DESC
LIMIT 3;

-- The next page of the same list.
SELECT title, year
FROM books
ORDER BY year DESC
LIMIT 3 OFFSET 3;

NULLs and growing pages

Where does a missing year sort? SQLite puts NULL before numbers in ascending order; PostgreSQL puts it last unless told otherwise. If the placement matters, say so explicitly with NULLS FIRST/NULLS LAST where the engine supports it, or sort on a computed flag.

OFFSET pagination gets slower and less stable as the page number grows, because the database still walks the rows it is skipping. The scalable version is a keyset query — WHERE (year, id) < (lastYear, lastId) — but ORDER BY plus LIMIT is the right first tool, and the one to understand before optimizing.

Try these next

• Sort the books by title length, longest first, and keep the top three.
• Add a book with a NULL year and see where it lands in ascending and descending order.
• Build page three of the author list and compare it with LIMIT 3 OFFSET 6.