ORDER BY and LIMIT

A table is a set of rows. Sets have no order. If you do not write ORDER BY, the database is free to return rows in whatever order was cheapest today, and that can change when the data grows or the plan changes.

SELECT name, credit
FROM customers
ORDER BY credit DESC, name ASC;

Sorting on two columns means: by credit, highest first, and where two rows tie, by name. Without the tiebreak the order of tied rows is undefined.

Try it

Where NULLs go

PostgreSQL sorts NULL last when ascending and first when descending. MySQL and SQL Server do the opposite, which is exactly the kind of difference that bites on a migration. Say what you mean:

ORDER BY credit DESC NULLS LAST

LIMIT without ORDER BY

LIMIT 10 on its own means "any ten rows". It will look stable in development and stop being stable in production. If you are paginating, the order has to be deterministic, which usually means ending the sort on something unique such as the primary key.

LIMIT is how PostgreSQL and MySQL spell it. The SQL standard writes FETCH FIRST 10 ROWS ONLY, which PostgreSQL also accepts and MySQL rejects; SQL Server rejects both LIMIT and that form and writes SELECT TOP 10 ... instead.

Common mistakes

• Sorting numbers stored as text. Text sorts character by character, so '10' comes before '9'. Store numbers in a numeric column, or cast before sorting.

• Paginating on a column that is not unique. With ORDER BY created_at and two rows sharing a timestamp across a page boundary, one row can appear on both pages and another on neither. End the sort on a key.
• Assuming NULLS LAST is portable. PostgreSQL and Oracle accept it; MySQL and SQL Server do not, and there you sort on an expression such as CASE WHEN credit IS NULL THEN 1 ELSE 0 END first.

What interviewers ask

• Is the order of rows from SELECT without ORDER BY guaranteed? No. A table is a set; the order is whatever the chosen plan produced.
• How do you page through a large result reliably? A deterministic ORDER BY ending on a unique column; for deep pages, keyset pagination (WHERE (created_at, id) > (last_created_at, last_id)) instead of a big OFFSET, which still reads every skipped row.
• Where do NULLs sort? It depends on the engine — PostgreSQL treats them as larger than any value, MySQL and SQL Server as smaller — so say it explicitly.