LIMIT, OFFSET and cursor boundaries

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

LIMIT, OFFSET and cursor boundaries

Concept and contract

Define a stable page and distinguish a fixed snapshot from a changing catalogue.

A page is a slice of an ordered population. The worked query returns the first three active identities in ascending order. Its contract contains three parts: which rows qualify, how they are ordered and how many are requested. Omitting one makes the result ambiguous.

For the unchanged teaching fixture, the first page is 1, 2, 3 and the next page is 4, 6, 8. We are not claiming that an offset is stable under concurrent inserts or deletions; this first example deliberately uses a fixed snapshot.

Mechanism and worked example

LIMIT caps output rows and OFFSET skips a count of ordered rows.

LIMIT caps output rows and OFFSET skips a count of ordered rows. It does not mean skip identities smaller than a numeric threshold. With missing identities or filtered rows, an offset and an identity boundary describe different operations and must not be confused.

A stable ORDER BY is required on every page. The engine may still do considerable work before producing a short output, so a small limit alone is not a guarantee of a cheap query. Measure real plans when discussing performance.

Complete worked example

sql

SELECT id FROM roles WHERE active = 1
ORDER BY id LIMIT 3 OFFSET 0;

Reasoning: The first ordered active page contains 1, 2 and 3.

Counterexamples and common mistakes

The counterexample uses descending identity order instead of the requested ascending order.

The counterexample uses descending identity order instead of the requested ascending order. It returns 8, 6 and 4, a stable answer to a different page contract. Another mistake changes country or active filters between pages, producing gaps or repetitions even if each individual page has a valid ORDER BY.

For a changing feed, offset pages can shift when earlier rows appear or disappear. Keyset pagination instead uses a boundary compatible with the ordering. It still needs an explicit consistency policy; a cursor alone cannot freeze mutable records into a snapshot.

Counterexample: a plausible but wrong query

sql

SELECT id FROM roles WHERE active = 1 ORDER BY id DESC LIMIT 3;

Reasoning: A different ordering returns a different slice; an omitted ordering would be unspecified.

Independent practice: small query

Given an ascending identity cursor after role 3, return at most three later active roles.

Given an ascending identity cursor after role 3, return at most three later active roles. Predict 4, 6, 8. Use a strict inequality so the boundary row is not repeated, and preserve the active predicate from the first page.

This exercise uses a single increasing immutable key to keep the first cursor task small. If ordering by publication date and identity, both components belong in the boundary. Do not generalize id-only comparison to a different ordering rule.

Step 1: Keep the original active condition. Step 2: Add the strict identity boundary.

Practice starter: solve before reading the solution

sql

SELECT id FROM roles
WHERE active = 1 AND /* cursor boundary */
ORDER BY id LIMIT 3;

Reasoning: Use a strict boundary while retaining the active filter.

Hint 1: The previous page ends at id 3.
Hint 2: Use > rather than >= before the same ascending ORDER BY.

Solution, tests and trade-offs

The solution uses id greater than 3, orders by id and limits to three.

The solution uses id greater than 3, orders by id and limits to three. It avoids repeating role 3 and does not assume identity 5 is active. Comparing exact identities tests the boundary and the population simultaneously.

A useful extra test follows pages until the returned list is empty and checks unique membership against the fixed expected set. Put a maximum iteration count into that test so a broken cursor cannot loop forever. Keep snapshot changes as a separate scenario.

Practice solution and acceptance checks

sql

SELECT id FROM roles
WHERE active = 1 AND id > 3
ORDER BY id LIMIT 3;

Reasoning: The next cursor page is exactly 4, 6 and 8.

Interview reasoning and next step

In interviews, explain offset simplicity, changing-data consistency and keyset boundaries as trade-offs.

In interviews, explain offset simplicity, changing-data consistency and keyset boundaries as trade-offs. Avoid saying cursors eliminate every duplicate or missing record in all systems. Rows whose sort keys change can still move across a boundary unless the product chooses a stronger snapshot mechanism.

The small exercise establishes a strict boundary and total ordering. Later analytics topics use windows for ranking inside a result, which is a different responsibility from paging a public API. The next lesson distinguishes duplicate output values from duplicate entities.

Optional stretch — after the core task works: Write the two-column boundary for published DESC, id DESC.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Define a stable page and distinguish a fixed snapshot from a changing catalogue.