Sorting and pagination

Draft

SQL tables do not promise a natural order. Use ORDER BY when order matters, then LIMIT and OFFSET when you want a page.

Sort rows

ORDER BY makes result order explicit instead of trusting table storage order.

Loaded Database:sorting-and-pagination.database-init

SELECT name, starts_at
FROM events
ORDER BY starts_at DESC;

name	starts_at
Join clinic	2026-01-05T09:00:00.000Z
Aggregation lab	2026-01-04T09:00:00.000Z
Index workshop	2026-01-03T09:00:00.000Z
Schema review	2026-01-02T09:00:00.000Z
Kickoff	2026-01-01T09:00:00.000Z

Sort  (cost=1.11..1.12 rows=5 width=21)
  Sort Key: starts_at DESC
  ->  Seq Scan on events  (cost=0.00..1.05 rows=5 width=21)

Limit and offset

LIMIT and OFFSET can fetch one page from a larger ordered result.

Loaded Database:sorting-and-pagination.database-init

SELECT name, starts_at
FROM events
ORDER BY starts_at DESC
LIMIT 2 OFFSET 2;

name	starts_at
Index workshop	2026-01-03T09:00:00.000Z
Schema review	2026-01-02T09:00:00.000Z

Limit  (cost=1.11..1.12 rows=2 width=21)
  ->  Sort  (cost=1.11..1.12 rows=5 width=21)
        Sort Key: starts_at DESC
        ->  Seq Scan on events  (cost=0.00..1.05 rows=5 width=21)

Sort with an index

An index with the same order can let PostgreSQL avoid a separate sort step.

Loaded Database:sorting-and-pagination.database-init

SELECT name, starts_at
FROM events
ORDER BY starts_at DESC
LIMIT 1;

name	starts_at
Join clinic	2026-01-05T09:00:00.000Z

Limit  (cost=1.07..1.08 rows=1 width=21)
  ->  Sort  (cost=1.07..1.09 rows=5 width=21)
        Sort Key: starts_at DESC
        ->  Seq Scan on events  (cost=0.00..1.05 rows=5 width=21)

What We Learned

ORDER BYdefines the order of result rows.
LIMITcaps how many rows are returned.
OFFSETskips rows before returning a page.
Index-backed ordercan avoid a separate sort when the index matches the order.