SQL pagination: LIMIT, OFFSET and stable results

By Anil Gautam · Published and reviewed September 20, 2026

Give paginated SQL results a stable ORDER BY. Without it, LIMIT and OFFSET don’t tell the database which rows belong on each page. Start with a unique sort order, then choose the paging method that fits your query.

Start with an ordered page

The examples use a made-up orders table whose numeric id is unique and never NULL.

SELECT id, customer_id
FROM orders
ORDER BY id
LIMIT 50 OFFSET 100;

The query skips 100 ordered rows and returns up to 50. If you sort by a non-unique field such as creation time, add a unique tiebreaker. PostgreSQL explains both the need for order and the work required by skipped rows in its LIMIT and OFFSET documentation.

Use an ID cursor for sequential browsing

If the previous page ended at ID 150, the next page can request IDs greater than that value:

SELECT id, customer_id
FROM orders
WHERE id > 150
ORDER BY id
LIMIT 50;

Use the last ID actually returned, not the page number multiplied by the page size. IDs can have gaps. This example assumes increasing ID order; a descending sort reverses the comparison. A composite sort requires a matching composite cursor and careful handling of NULLs. Evaluate the query plan and indexes for the actual filter.

Choose based on navigation requirements

OFFSET is straightforward when the user needs a numbered page and the dataset is modest. A cursor fits “next page” and batched processing well, but does not naturally jump to an arbitrary page number. Neither choice by itself freezes a dataset that other sessions are changing.

For a reproducible export or reconciliation, choose an appropriate snapshot or consistency strategy rather than stitching together pages from unrelated moments. Clearly record whether the goal is a live browsing screen or a fixed report.

Distinguish paging from completeness

A grid showing 50 rows may be showing one page, a fetch limit or an already-filtered result. Before exporting, inspect the query, active filter, sort and platform's export scope. PlainDB table exports can fetch all matching rows on both platforms. Neither a page count nor a screenshot proves a complete database backup.

To compare approaches, use the same dataset and predicate, inspect the plan, and measure later pages as well as the first. Avoid a universal performance claim based on one tiny example.

Continue reading

Inspect the query plan · Validate an export

For the desktop workflow, compare plainDB features on Mac and Windows or download the 14-day trial.