ORDER BY, LIMIT, and Pagination

~12 min · queries, ordering

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Order is not free

SQL makes no ordering guarantee unless you explicitly use ORDER BY. A query that "happened to come back sorted" yesterday will silently change order tomorrow when the planner picks a different access path. Always use ORDER BY when order matters.

Sorting

Ascending is the default; ORDER BY price DESC for descending.
Multiple columns sort lexicographically: ORDER BY category, price DESC.
NULLS FIRST / NULLS LAST controls where NULLs land (PostgreSQL extension).
You can sort by computed expressions or column position (ORDER BY 2 DESC) — both are legal but expression is more readable.

Pagination — and why OFFSET stops scaling

LIMIT 10 OFFSET 100 still has to read and discard the first 100 rows. At page 1000 you're discarding 10,000 rows per request. Cursor-based (keyset) pagination is the scalable answer: filter on the last seen value, then LIMIT. No OFFSET, constant cost regardless of page depth.

Code

Sorting·sql

SELECT name, price, created_at
FROM   products
ORDER  BY category ASC, price DESC NULLS LAST;

OFFSET pagination (slow at scale)·sql

-- Page 1
SELECT * FROM products ORDER BY created_at DESC LIMIT 20;
-- Page N
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET (N-1)*20;

Keyset pagination (constant cost)·sql

-- First page
SELECT id, name, created_at
FROM   products
ORDER  BY created_at DESC, id DESC
LIMIT  20;

-- Next page: pass the last (created_at, id) as the cursor
SELECT id, name, created_at
FROM   products
WHERE  (created_at, id) < ('2026-04-01 00:00:00', 12345)
ORDER  BY created_at DESC, id DESC
LIMIT  20;

External links

Use The Index, Luke — Pagination

Exercise

Take a query that paginates with OFFSET. Convert it to keyset pagination with a (sort_column, id) composite cursor. Confirm the same results return regardless of which 'page' you request directly.