SQL / SORTING, LIMITING, AND BRANCHING OUTPUT
Paging through results with LIMIT and OFFSET
Turn a page number into a stable LIMIT/OFFSET query, spot the last page, and know when deep offsets should become keyset paging.
What you will learn
- Translate a page number into OFFSET (page - 1) * page_size; page 1 means OFFSET 0
- End ORDER BY with a unique column so pages never overlap or drop rows
- Fetch page_size + 1 rows to learn whether a next page exists
- Replace a deep OFFSET with a WHERE bookmark on the sort key when paging slows down
Understanding Paging through results with LIMIT and OFFSET
LIMIT and OFFSET act on the finished, ordered result of the query: rows are filtered by WHERE, grouped, sorted by ORDER BY, and only then does OFFSET throw away rows from the front of that list while LIMIT keeps a fixed count of what remains. Paging is arithmetic on that skip count, so with a page size of 20, page 1 is OFFSET 0, page 2 is OFFSET 20, and page n is OFFSET (n - 1) * 20. Reading OFFSET as "skip this many rows" rather than "start at row number this" keeps the off-by-one out of the calculation.
The whole scheme rests on the ORDER BY producing one definite order. Rows that tie on every sort column have no defined order among themselves, and page 1 and page 2 are two separate queries that the engine may run with different plans, or against a table that changed in between, so tied rows can shuffle. When that happens one row appears on both pages while another is never shown at all. Ending ORDER BY with a column that is unique per row, usually the primary key, makes the order total and the page boundaries reproducible.
OFFSET does not let the engine skip work; it makes the engine do the work and then discard the result. Asking for page 5000 at size 20 means producing and ordering 100,020 rows to return the last 20, which is why deep pages get slow in a way the first few pages never hint at. When the interface only needs next and previous rather than a jump to an arbitrary page, remember the sort key of the last row shown and ask for rows past it, which lets an index seek straight to the page start and costs the same at any depth.
Pages are also snapshots taken at different moments. If rows are inserted or deleted between two requests, the row that was at position 20 may move to 21, and the user scrolling to page 2 will never see it.
CREATE TABLE player (id INTEGER, name TEXT, score INTEGER);
INSERT INTO player VALUES
(1,'Ada',90),(2,'Brin',75),(3,'Cyd',90),
(4,'Dev',60),(5,'Eli',75),(6,'Fay',42),(7,'Gus',88);
-- page 2 at a page size of 3: skip 1 * 3 rows, keep the next 3
SELECT id, name, score
FROM player
ORDER BY score DESC, id
LIMIT 3 OFFSET 3;OFFSET skips rows in an already-sorted result, so a page is only stable if the ORDER BY is unique, and a page only cheap if the offset is small.
Worked examples
Detecting the last page
Requesting one row more than the page size tells you whether a next page exists without a second query.
CREATE TABLE player (id INTEGER, name TEXT, score INTEGER);
INSERT INTO player VALUES
(1,'Ada',90),(2,'Brin',75),(3,'Cyd',90),
(4,'Dev',60),(5,'Eli',75),(6,'Fay',42),(7,'Gus',88);
-- page 3 at a page size of 3, asking for 4 rows
SELECT id, name, score
FROM player
ORDER BY score DESC, id
LIMIT 4 OFFSET 6;Example explained
Line 1OFFSET 6 discards exactly the six rows that pages 1 and 2 already showed.
Line 2LIMIT 4 is page_size + 1: if four rows came back you would display the first three and enable the next-page link.
Line 3Only one row is returned, so page 3 is the final page and it is partial.
Line 4Fewer remaining rows than LIMIT is never an error; the engine simply returns what is left.
Row and page totals for page links
A separate count gives the number of pages, using truncating integer division to round up.
CREATE TABLE player (id INTEGER, name TEXT, score INTEGER);
INSERT INTO player VALUES
(1,'Ada',90),(2,'Brin',75),(3,'Cyd',90),
(4,'Dev',60),(5,'Eli',75),(6,'Fay',42),(7,'Gus',88);
SELECT COUNT(*) AS total_rows,
(COUNT(*) + 3 - 1) / 3 AS total_pages
FROM player;Example explained
Line 1(COUNT(*) + page_size - 1) / page_size rounds up: (7 + 2) / 3 is 3, so seven rows fill three pages of three.
Line 2The count query must carry the same WHERE clause as the paged query, or the last page numbers you render will not exist.
Line 3This relies on / truncating between integers, which holds in PostgreSQL and SQLite; MySQL's / yields a decimal, so use DIV there.
Line 4COUNT(*) scans the whole matching set, so on large tables it is often replaced by the page_size + 1 trick above.
Keyset paging instead of a deep offset
Continuing from the last key seen returns the next page without producing and discarding earlier rows.
CREATE TABLE player (id INTEGER, name TEXT, score INTEGER);
INSERT INTO player VALUES
(1,'Ada',90),(2,'Brin',75),(3,'Cyd',90),
(4,'Dev',60),(5,'Eli',75),(6,'Fay',42),(7,'Gus',88);
-- the previous page ended at id 3
SELECT id, name, score
FROM player
WHERE id > 3
ORDER BY id
LIMIT 3;Example explained
Line 1WHERE id > 3 uses the last id of the previous page as a bookmark, so no skip count is needed.
Line 2With an index on id the engine seeks directly to the first qualifying row, so page 1000 costs what page 2 costs.
Line 3The ORDER BY column and the bookmark column must be the same, otherwise the bookmark does not mark a position in the result.
Line 4The trade-off is navigation only: this pattern gives next and previous, not a jump to page 7.
Important notes
LIMIT and OFFSET are applied after DISTINCT, GROUP BY, HAVING and ORDER BY, so LIMIT 3 on a grouped query returns 3 groups, not 3 underlying rows.
MySQL accepts only literals or bound parameters after LIMIT and OFFSET, not arithmetic such as (2-1)*3; compute the offset in code and bind it rather than pasting a page number from a URL into the SQL text.
Common mistakes
Paging on an ORDER BY with ties and no unique tie-breaker: it looks correct on a small test table, then in production one row shows up on two pages and another vanishes.
Computing OFFSET as page * page_size instead of (page - 1) * page_size, so page 1 already skips a page and the top-ranked rows are unreachable.
Assuming OFFSET 100000 is cheap because the rows are "skipped": the engine still sorts and materialises every skipped row, making the deepest pages the slowest queries in the application.
Try it yourself
Change, predict, then run
Create the player table from the examples and write the page-2 query for a page size of 2, ordered by score DESC then id. Then change only the OFFSET so the query returns Fay as its single row, and work out which page number that offset corresponds to.
Open the SQL workspaceCheck your understanding
A listing runs SELECT ... ORDER BY score DESC LIMIT 10 OFFSET 10 for page 2, on a table where many players share the same score. Users report seeing the same player on page 1 and page 2. What is the most likely cause?
- The ORDER BY does not uniquely determine the order, so the two page queries can arrange tied rows differently
- OFFSET is off by one and should be OFFSET 11 for the second page of ten
- LIMIT is applied before ORDER BY, so each page is sorted only within itself
- The score column lacks an index, and OFFSET needs an index to count rows correctly
Show answer
Rows that tie on score have no defined relative order, and page 1 and page 2 are separate queries that may resolve those ties differently, so a tied row can land in both windows while another lands in neither; adding a unique last ORDER BY term such as id fixes it. OFFSET 11 is not the issue: an off-by-one would shift every boundary by exactly one row rather than duplicate rows inside tied groups, and OFFSET 10 correctly skips the ten rows page 1 displayed. LIMIT is applied after ORDER BY, and an index only affects speed, not which rows a correctly ordered query returns.