How do LIMIT and OFFSET work together to show one page of results?

Think of the query as three steps: first put the results in a defined order, then skip some rows, then take a limited number. ORDER BY defines the order; OFFSET says how many rows to skip; LIMIT says how many rows to return after skipping. SQLite documents that LIMIT N OFFSET M skips the first M rows of the ordered results and returns the next N, or fewer if fewer remain. [S1]

Here is a complete, invented catalog example you can run in SQLite:

CREATE TABLE catalog (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

INSERT INTO catalog (id, title) VALUES
  (1, 'Apple'),
  (2, 'Birch'),
  (3, 'Cedar'),
  (4, 'Dune'),
  (5, 'Elm');

SELECT title
FROM catalog
ORDER BY title
LIMIT 2 OFFSET 2;

Exact output:

Cedar
Dune

The titles sort as Apple, Birch, Cedar, Dune, Elm. The offset skips Apple and Birch; the limit takes the next two. The catalog and output are illustrative, not a claim about any real dataset.

Without ORDER BY, SQLite does not define the order of multiple returned rows. [S1] That means “skip two, then take two” would not reliably mean “show the third and fourth titles alphabetically.” Choose the order that makes sense for your page—such as title, date, or another field—and state it in the query.

If two rows have the same sort value, specify a further tie-breaker, preferably a unique column such as an ID. For example:

SELECT id, title
FROM catalog
ORDER BY title, id
LIMIT 2 OFFSET 2;

This makes the ordering rule explicit when titles match. The source supports the need for a tie-breaker to define which tied rows come first; it does not establish that pages remain identical if the catalog changes between separate page queries. If rows are added, removed, or edited between requests, the page contents may differ.

For pages numbered from 1, calculate the offset as (page number - 1) × page size. With a page size of two:

1. Page 1: LIMIT 2 OFFSET 0 returns Apple and Birch.
2. Page 2: LIMIT 2 OFFSET 2 returns Cedar and Dune.
3. Page 3: LIMIT 2 OFFSET 4 returns Elm, because only one row remains.

SQLite returns fewer than the limit when fewer rows remain. [S1] If your application numbers pages starting at 0 instead, the offset is page number × page size. Keep the numbering convention clear so you do not accidentally skip the first page.

Use this pattern, replacing the column names and page values with your own:

SELECT id, title
FROM catalog
ORDER BY title, id
LIMIT 2 OFFSET 2;

Before relying on a page, check that the ordering expresses your intended sequence, that the tie-breaker resolves equal sort values, and that the offset matches your page-number convention. Run the same query on a small, known set of rows and count from the start of its ordered output: rows before the offset should be skipped, and no more than the limit should appear. Those checks verify the query’s intended slice for that data; they do not guarantee that results stay fixed if the catalog changes between page queries. The cited behavior here is SQLite-specific, so do not assume another database uses identical syntax or behavior. [S1]