SQL ORDER BY and LIMIT
A database doesn't promise any row order unless you ask for one. ORDER BY sorts the result and LIMIT keeps the first few rows. Together they answer every “top 10” question.
Sort ascending or descending
ORDER BY column sorts smallest to largest (ASC is the default). Add DESC to reverse it. Sort by several columns by listing them: the second breaks ties in the first.
SELECT name, species, age FROM pets ORDER BY species, age DESC;
Keep the top rows with LIMIT
LIMIT n keeps the first n rows after sorting. Without ORDER BY, LIMIT returns an arbitrary handful, which is fine for peeking at a table but wrong for “the 3 oldest”.
SELECT name, age FROM pets ORDER BY age DESC LIMIT 3;
Pages with OFFSET
OFFSET n skips rows before LIMIT starts counting, which is the classic way to show page 2, 3 and so on. Always sort by something unique (like id) so pages don't overlap.
SELECT id, name FROM pets ORDER BY id LIMIT 5 OFFSET 5;
Where do NULLs go?
PostgreSQL treats NULL as larger than everything, so NULLs come last in ascending order. SQLite treats NULL as smallest, so they come first. Say what you mean with NULLS FIRST or NULLS LAST, which work in both.
SELECT name, breed FROM pets ORDER BY breed NULLS LAST;
Sort by an expression or alias
You can sort by a calculation, or by an alias defined in SELECT.
SELECT name, LENGTH(name) AS letters FROM pets ORDER BY letters DESC, name;
Common mistakes
Expecting results in insertion order without ORDER BY.
LIMIT without ORDER BY when you need the top N.
Deep OFFSET pages on big tables: the database still reads every skipped row. Use keyset pagination (
WHERE id > last_seen_id) instead.
Questions
What's the default sort direction?+
Ascending (ASC): smallest numbers, earliest dates and A before Z first.
Can I ORDER BY a column number?+
Yes, ORDER BY 2 sorts by the second selected column, but it breaks silently when someone edits the SELECT list. Prefer names.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.