ORDER BY and LIMIT

Without order by, there is no order

A table is a set of rows, not a list. Rows come back in whatever order the database found cheapest today — which is often insertion order, right up until an index, a join or a new version changes it. If the order matters, say so. Every "it worked on my machine" pagination bug starts here.

order by price desc
order by style, price desc            -- style A→Z, and inside each style, dearest first
order by price * 1.18                 -- an expression
order by with_vat                     -- a name invented in select: allowed here, not in where
order by 2                            -- the second selected column. clever, and unreadable

Ties need a tiebreak

Two rows with the same price can come back in either order, and on a paged screen that means a row can appear on page 1 and again on page 2 while another is never seen at all. Add something unique — usually the id — as the last sort key. It costs nothing and makes the result deterministic.

order by price desc, id;

Where NULL goes

order by city                 -- SQLite, MySQL: nulls first · PostgreSQL, Oracle: nulls last
order by city nulls last      -- the standard (SQLite 3.30+, PostgreSQL, Oracle)
order by city is null, city   -- works everywhere: false (0) sorts before true (1)

limit and offset

limit is applied last of all — after the sort, which is why the database still has to sort everything before it can hand you five rows. That is fine on ten doors and ruinous on ten million; the Indexes lesson explains how an index turns the sort into a walk.

limit 5              -- the first five, after sorting
limit 4 offset 4     -- rows 5 to 8: "page 2", four to a page
limit 4, 4           -- MySQL shorthand for the same thing, in the other order

-- the standard spelling, which SQL Server and Oracle use
offset 4 rows fetch next 4 rows only;

Deep pages: why offset gets slower

offset 100000 means "find one hundred thousand rows, throw them away, then read twenty". The work grows with the page number. For feeds and long lists, remember where you stopped instead: this is called keyset pagination, and it is one comparison against an index.

select name, price from doors
where  (price, id) < (1650, 3)      -- the last row of the previous page
order by price desc, id desc
limit  4;