LIMIT in SQL: ordering, OFFSET and stable pagination

LIMIT restricts the number of rows returned by a query. Before adding it, I define their order. Selecting the newest publications also requires a rule for entries sharing the same date. Without one, two valid executions can select different records at a page boundary.

The examples were executed in SQLite 3.53.4 through Node 24 using synthetic data. The dialect comparison below relies on vendor documentation and helps identify the appropriate syntax. It does not claim execution on every listed engine.

Prepare a small publication list

Run this block in a new, empty database. Dates use the same ISO format, so text sorting corresponds to day order here. The sorting key excludes NULL; missing dates would require an additional rule.

-- example: fixture
CREATE TABLE entries (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    published_at TEXT NOT NULL
);
INSERT INTO entries VALUES
    (1, 'Alpha', '2026-10-20'),
    (2, 'Beta', '2026-10-22'),
    (3, 'Gamma', '2026-10-22'),
    (4, 'Delta', '2026-10-23'),
    (5, 'Epsilon', '2026-10-24'),
    (6, 'Zeta', '2026-10-24');

Each row represents one publication. Within the same date, the higher ID comes first. This is an explicit rule for the exercise. Another system might require a precise publication timestamp or a different tie-breaker.

LIMIT with an unambiguous ORDER BY

I retrieve the first two rows in the defined order:

-- example: first-page
SELECT id, title, published_at
FROM entries
ORDER BY published_at DESC, id DESC
LIMIT 2;

The date alone cannot order publications from the same day. Adding the ID makes the order unique. The importance of this rule with LIMIT is also explained in the PostgreSQL documentation. Without ORDER BY, I would not call retrieved rows the newest or the first according to some assumed default table order.

LIMIT is a maximum. If the result contains one row, LIMIT 2 returns one. It does not fill missing positions. Nor does it automatically select a representative sample: the newest two publications are a particular slice of the data that may differ from the rest.

OFFSET skips result rows

With a page size of two, the second page skips the first two positions:

-- example: second-page
SELECT id, title, published_at
FROM entries
ORDER BY published_at DESC, id DESC
LIMIT 2 OFFSET 2;

OFFSET counts rows, rather than identifier values. For pages numbered from one, use offset = (page_number - 1) * page_size. Subtract one from the page number first, then multiply by the page size. The third page therefore uses OFFSET 4 and contains IDs 2 and 1. The first page uses OFFSET 0 or omits that part of the query.

If you add WHERE, pagination applies after filtering. It does not retrieve two records first and then discard those failing the condition. For example, filtering for 22 October or later leaves five records. The third page then contains only ID 2.

Why adding data changes pages

Suppose you read the first page, containing IDs 6 and 5. A new publication then appears with ID 7 and a date of 25 October. The new order begins 7, 6, 5, 4. Retrieving the second page using OFFSET 2 now returns 5 and 4. You see record 5 again even though ORDER BY is still correct.

The data changed between reads. A unique order resolves ties within a result, but does not freeze the whole list while you browse. If you need a reproducible export, define consistent-read rules or a stable data slice. LIMIT alone cannot provide that guarantee.

Continue from the last key

For scrolling through a list, I can retain the date and ID of the previous page’s last row. After ID 5 on 24 October, I request rows with an earlier date, or a lower ID on the same date. This is keyset pagination, often called cursor pagination.

-- example: keyset
SELECT id, title, published_at
FROM entries
WHERE published_at < '2026-10-24'
   OR (published_at = '2026-10-24' AND id < 5)
ORDER BY published_at DESC, id DESC
LIMIT 2;

On the initial data, this matches the second OFFSET page. After inserting the newer entry described above, it still returns 4 and 3 because the new row falls before the saved position. The cursor must contain both ordering components. Using only id < 5 would express a different rule if IDs and dates did not increase together.

This approach also has limits. Updating an existing entry’s date can move it to another page, and deleting data changes the available set. Keyset pagination also makes jumping directly to an arbitrary page number harder without a known starting position. I choose it around how the list will be used, rather than treating it as an automatic replacement for every OFFSET.

Boundary cases and query cost

In SQLite, LIMIT 0 returns an empty result:

-- example: zero-limit
SELECT id FROM entries ORDER BY id LIMIT 0;

Skipping more rows than exist also returns no rows:

-- example: past-end
SELECT id FROM entries ORDER BY id LIMIT 2 OFFSET 100;

Use non-negative integers within your application’s intended bounds. Negative values and NULL behave differently across engines; do not transfer those assumptions without checking. In SQLite, a negative limit means no upper bound, as described in the LIMIT documentation. I do not use this as portable syntax.

A large OFFSET may require processing many skipped records. LIMIT also does not guarantee a cheap query when earlier operations require an expensive join or sort. I would examine the plan and an index appropriate to the filter and ordering. This example includes no performance benchmark, so I attach no numerical speed-up claim to it.

Dialect differences

LIMIT is supported by SQLite, PostgreSQL and MySQL. The examples use the readable form with a separate OFFSET. I avoid mixing it with the comma variant, where the numbers’ positions can easily cause confusion.

In SQL Server, pagination uses ORDER BY with OFFSET followed by an optional FETCH. The separate TOP construct limits the beginning of a result, but is not the LIMIT keyword. In Oracle 19c, the row-limiting clause also places OFFSET before FETCH. The reversed order in the earlier Polish article was incorrect. Check your specific server version when porting a query.

Exercise: inspect the page boundary

Change the page size to three. The first page should contain 6, 5, 4, and the second should contain 3, 2, 1. Then insert the described ID 7 record and compare the second OFFSET page with continuation from the previous page’s last key. Explain the discrepancy instead of changing your expectation simply because the database returned different rows.

If duplicates originate in the data rather than shifting pages, see SELECT DISTINCT. First define what a result row means, then its order, and finally the portion you want to retrieve.