ORDER BY, LIMIT and OFFSET

Why ORDER BY?

A table is a set of rows with no built-in order. The order you see without ORDER BY is whatever was fastest for the database today, and it can change after an update or a new index. If the order matters, say so with ORDER BY.

SELECT name, price FROM products ORDER BY price DESC;
-- Running Shoes        | 2499
-- Bluetooth Earbuds    | 1999
-- Pressure Cooker 3L   | 1450
-- ...

Sorting by many columns

List several columns, each with its own direction. The second column is used only to break ties in the first, the third only to break ties in the first two, and so on.

SELECT name, city, marks FROM students ORDER BY city, marks DESC;
-- Priya Nair  | Chennai   | 91
-- Divya Kumar | Delhi     | 88
-- Ravi Kumar  | Delhi     | 67
-- ...
-- Asha Verma  | Pune      | 76
-- Imran Khan  | Pune      | 58
-- Sneha Patil | Pune      | NULL

Text is sorted by character codes by default in SQLite, so capital letters come before small letters: 'Zebra' sorts before 'apple'. Store names in a consistent case, or sort with ORDER BY name COLLATE NOCASE.

Where do NULLs go?

SQLite treats NULL as smaller than every value. So NULLs come first in ASC and last in DESC. Other databases differ: PostgreSQL and Oracle put NULLs last in ASC, while MySQL and SQL Server put them first. SQLite (since 3.30) and PostgreSQL let you choose with NULLS FIRST or NULLS LAST.

SELECT name, marks FROM students ORDER BY marks NULLS LAST;
-- Arjun Reddy | 45
-- ...
-- Priya Nair  | 91
-- Sneha Patil | NULL

LIMIT: top-N lists

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
-- Running Shoes      | 2499
-- Bluetooth Earbuds  | 1999
-- Pressure Cooker 3L | 1450

OFFSET: pages

OFFSET skips rows before LIMIT starts counting. Apps use it for pages: with page_size rows per page, page n is LIMIT page_size OFFSET (n − 1) × page_size. Page 1 has OFFSET 0, page 2 OFFSET page_size, and so on.

-- 3 students per page, sorted by name. Page 2:
SELECT id, name FROM students ORDER BY name LIMIT 3 OFFSET 3;
-- 4 | Imran Khan
-- 8 | Karan Mehta
-- 3 | Priya Nair

Ties

Anita Desai and Vikram Singh both earn 95000. In ORDER BY salary DESC LIMIT 5, which of them is fifth? The database may pick either, and the choice can change. Add a tie-breaker column, such as ORDER BY salary DESC, name, so the result is always the same. PostgreSQL 13 and later can also keep every tied row with FETCH FIRST 5 ROWS WITH TIES.

Other dialects

Common mistakes

• Using LIMIT without ORDER BY and expecting the "top" rows. Always sort first.
• Putting DESC once and expecting it to apply to every column. ORDER BY city, marks DESC sorts city ascending.
• Off-by-one page maths: page 2 with 10 per page is OFFSET 10, not OFFSET 20.
• Forgetting where NULLs land. Add NULLS LAST (or NULLS FIRST) when it matters.
• Writing ORDER BY 2 (the column position). It works, but breaks silently when someone changes the SELECT list. Use the column name.