LIMIT, OFFSET, FETCH, and Pagination Tradeoffs

Learning outcomes

Result limiting is not merely presentation. It creates a contract about which ordered rows are returned, how clients move through a large result, and how concurrent changes affect repeated requests.

LIMIT returns a result window

SELECT    sale_id,    customer_id,    sold_atFROM saleORDER BY    sold_at DESC,    sale_id DESCLIMIT 5;

The query first defines a deterministic sequence, then returns its first five rows. Without ORDER BY, “first” has no durable meaning.

OFFSET skips rows in the ordered result

SELECT    sale_id,    customer_id,    sold_atFROM saleORDER BY    sold_at DESC,    sale_id DESCLIMIT 3 OFFSET 3;

OFFSET 3 discards the first three rows of the ordered result and returns up to three rows after them.

Syntax varies across major engines

SELECT sale_id, sold_atFROM saleORDER BY sold_at DESC, sale_id DESCOFFSET 20 ROWSFETCH NEXT 10 ROWS ONLY;

SELECT TOP (10)    sale_id,    sold_atFROM saleORDER BY sold_at DESC, sale_id DESC;

Deterministic order is mandatory for pagination

ORDER BY sold_at DESCLIMIT 3 OFFSET 3;

Rows sharing sold_at can exchange positions. A row may appear on two pages or disappear between them even when the data is unchanged.

ORDER BY    sold_at DESC,    sale_id DESCLIMIT 3 OFFSET 3;

Offset pagination becomes expensive

SELECT sale_id, sold_atFROM saleORDER BY sold_at DESC, sale_id DESCLIMIT 25 OFFSET 100000;

Offset pagination is often acceptable for small administrative lists and shallow pages. It is less suitable for very large, frequently changing feeds.

Concurrent changes cause pagination drift

Suppose page 1 is fetched, then a new row is inserted at the beginning of the sort order. When page 2 uses the original offset, one previously seen row may shift onto page 2 and another row may be pushed to page 3.

Keyset pagination continues after the last key

Instead of counting and skipping earlier rows, the client sends the final sort key from the previous page.

SELECT sale_id, customer_id, sold_atFROM saleORDER BY sold_at DESC, sale_id DESCLIMIT 3;

Assume the last row returned has sold_at = '2026-08-05 09:00:00' and sale_id = 107.

SELECT sale_id, customer_id, sold_atFROM saleWHERE sold_at < '2026-08-05 09:00:00'   OR (sold_at = '2026-08-05 09:00:00' AND sale_id < 107)ORDER BY sold_at DESC, sale_id DESCLIMIT 3;

The continuation predicate mirrors the composite ordering. For ascending order, the comparison directions reverse.

Row-value comparison can simplify the cursor

SELECT sale_id, customer_id, sold_atFROM saleWHERE (sold_at, sale_id)    < ('2026-08-05 09:00:00', 107)ORDER BY sold_at DESC, sale_id DESCLIMIT 3;

SQLite and PostgreSQL support row-value comparisons. Portability and NULL behavior require care; the expanded OR form is more universally understandable.

Offset versus keyset pagination

Count queries are separate work

SELECT sale_id, customer_id, sold_atFROM saleWHERE customer_id = 1ORDER BY sold_at DESC, sale_id DESCLIMIT 10 OFFSET 0;SELECT COUNT(*) AS total_rowsFROM saleWHERE customer_id = 1;

A total count may be expensive for large filtered datasets. Decide whether the interface truly needs an exact total, an estimate, or only a “has more” signal.

Fetch one extra row to detect “has more”

-- UI page size is 3; fetch 4 rows.SELECT sale_id, customer_id, sold_atFROM saleORDER BY sold_at DESC, sale_id DESCLIMIT 4;

If four rows are returned, display the first three and use the fourth only to conclude that another page exists.

Practice lab

1. Return the three newest sales deterministically.
2. Return the second offset-based page with page size three.
3. Write the equivalent standard-style OFFSET/FETCH form.
4. Continue after the key ('2026-08-05 09:00:00', 107) with keyset pagination.
5. Return four rows so a three-row interface can detect another page.

SELECT sale_id, customer_id, sold_atFROM saleORDER BY sold_at DESC, sale_id DESCLIMIT 3;SELECT sale_id, customer_id, sold_atFROM saleORDER BY sold_at DESC, sale_id DESCLIMIT 3 OFFSET 3;-- Standard-style syntax for engines that support it:SELECT sale_id, customer_id, sold_atFROM saleORDER BY sold_at DESC, sale_id DESCOFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;SELECT sale_id, customer_id, sold_atFROM saleWHERE sold_at < '2026-08-05 09:00:00'   OR (sold_at = '2026-08-05 09:00:00' AND sale_id < 107)ORDER BY sold_at DESC, sale_id DESCLIMIT 3;SELECT sale_id, customer_id, sold_atFROM saleORDER BY sold_at DESC, sale_id DESCLIMIT 4;

Common failures

Limiting without ORDER BY

The selected subset is not stable.

Ordering by a non-unique key

Ties can migrate between pages.

Using deep offsets for large feeds

The server may repeatedly process and discard a large prefix.

Ignoring concurrent change

Offset pages can duplicate or skip rows as the dataset changes.

Exposing raw cursor data carelessly

Applications should validate and often encode cursor state rather than trusting arbitrary client predicates.

Chapter 5 summary

• WHERE filters rows through predicates that must evaluate TRUE.
• AND, OR, and NOT require explicit grouping and NULL-aware reasoning.
• IN, BETWEEN, and LIKE simplify membership, range, and pattern requirements when edge cases are controlled.
• ORDER BY defines row sequence; a unique tie-breaker makes it deterministic.
• LIMIT/OFFSET is simple, while keyset pagination scales and resists drift better for large changing feeds.

Chapter 6 introduces functions, NULL-handling expressions, CASE, and clean derived columns.

References

• SQLite: LIMIT clause
• PostgreSQL: LIMIT and OFFSET
• Microsoft Learn: ORDER BY, OFFSET, and FETCH
• Oracle Database SQL Language Reference: SELECT