OFFSET usage and examples

Overview

OFFSET works with LIMIT by ordering a result and then skipping a requested number of rows. Without a unique, stable ORDER BY key, rows may repeat or disappear between page requests. Large offsets still require the database to locate and discard earlier rows, so deep pagination often needs a cursor predicate instead.

Basic syntax

SELECT * FROM orders LIMIT 10 OFFSET 20;

Examples

OFFSET example

The example skips 20 ordered rows and returns the next 10. Real pagination must add a stable ORDER BY instead of relying on physical table order.

Sample table

Table：orders

orders
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Run result

result
id	customer_id	product_id	name	department	amount	status	created_at	discount

Common issues

Why does OFFSET pagination need stable ordering?

If ORDER BY does not uniquely determine order, tied rows may be repeated or skipped across pages. Add a unique tie-breaker such as the primary key.

Why does deep pagination get slower?

The database usually still locates and skips preceding rows. For deep pages, consider cursor pagination using the last row's sort key.

What if rows are inserted or deleted while paging?

OFFSET selects by position, so earlier changes shift later pages. Consider cursor pagination or snapshot reads when continuity matters.

Keep reading

SELECT WHERE DISTINCT ORDER BY LIMIT LIKE BETWEEN IS NULL

OFFSET