LIMIT and OFFSET

LIMIT restricts how many rows you get.

OFFSET skips a number of rows first.

Core Concepts

flowchart LR
  A[Sorted rows] --> B[Apply OFFSET]
  B --> C[Skip first N rows]
  C --> D[Apply LIMIT]
  D --> E[Return next M rows]

Clause	Purpose
LIMIT n	Return at most n rows
OFFSET n	Skip first n rows

Code Examples

-- Setup example table.
CREATE TABLE page_demo (
  id    INTEGER PRIMARY KEY,
  label TEXT NOT NULL
);

INSERT INTO page_demo (label)
VALUES ('A'), ('B'), ('C'), ('D'), ('E');

Expected output
Table created and five rows inserted.

-- Get two rows after skipping the first two.
SELECT id, label
FROM page_demo
ORDER BY id
LIMIT 2 OFFSET 2;

id	label
3	C
4	D

SQLite-Specific Nuances

SQLite Nuance

For stable pagination, combine LIMIT/OFFSET with an explicit ORDER BY.

Common Pitfalls / Best Practices

Pitfall

Using LIMIT without ORDER BY and expecting consistent page order.

Best Practice

Always sort by a deterministic key (often primary key) before paginating.

Quick Challenge

Return only the first three rows from page_demo ordered by id.

View Solution

SELECT id, label
FROM page_demo
ORDER BY id
LIMIT 3;

id	label
1	A
2	B
3	C