SQLite LIMIT and OFFSET: Paginate and Slice Result Sets

LIMIT Caps the Number of Rows

LIMIT is the simplest knob in SQL: it tells SQLite "give me at most this many rows." Stick it at the end of a SELECT and you'll get back up to that many results — no more, possibly fewer if the table doesn't have enough rows to satisfy it.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    year INTEGER
);

INSERT INTO books (title, year) VALUES
    ('The Pragmatic Programmer', 1999),
    ('Code', 1999),
    ('Refactoring', 1999),
    ('Clean Code', 2008),
    ('Designing Data-Intensive Applications', 2017);

SELECT title, year FROM books LIMIT 3;

You get the first three rows back. Which three, exactly? That's the catch — without an ORDER BY, SQLite picks whatever order it finds convenient. Today it might be insertion order; tomorrow, after an update or an index change, it might not. LIMIT on its own is fine for "show me a sample," but the moment the order matters, you need to make it explicit.

OFFSET Skips Rows From the Front

Pair LIMIT with OFFSET and you can ask for a slice from the middle of the result set. OFFSET k discards the first k rows; LIMIT n then returns up to n rows from what's left.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    year INTEGER
);

INSERT INTO books (title, year) VALUES
    ('The Pragmatic Programmer', 1999),
    ('Code', 1999),
    ('Refactoring', 1999),
    ('Clean Code', 2008),
    ('Designing Data-Intensive Applications', 2017);

SELECT title, year
FROM books
ORDER BY year, title
LIMIT 2 OFFSET 2;

That's "skip two rows, return the next two" — rows 3 and 4 of the ordered result. The mental model: WHERE filters, ORDER BY sorts, OFFSET skips, LIMIT caps. They run in that order, and they all matter.

Pagination Always Needs ORDER BY

The most common use of LIMIT and OFFSET is pagination — splitting a long list into pages of, say, 20 rows each. Page 1 is LIMIT 20 OFFSET 0, page 2 is LIMIT 20 OFFSET 20, and so on.

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT,
    created_at TEXT
);

INSERT INTO posts (title, created_at) VALUES
    ('First post', '2024-01-01'),
    ('Second post', '2024-01-02'),
    ('Third post', '2024-01-03'),
    ('Fourth post', '2024-01-04'),
    ('Fifth post', '2024-01-05');

-- Page 2, with 2 posts per page
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 2 OFFSET 2;

Two things to notice. First, the ORDER BY is non-negotiable — without it, "page 2" has no defined meaning and rows can shuffle between page loads. Second, the sort key includes id as a tiebreaker. If two posts share a created_at, you need a unique column to give them a deterministic order, otherwise their positions can swap and a row can leak across pages.

Rule of thumb: order by something unique, or order by your sort column plus a unique tiebreaker.

A Shorthand: LIMIT n, m

SQLite supports an older comma syntax for backwards compatibility with MySQL: LIMIT offset, count. It means the same as LIMIT count OFFSET offset, but the order is reversed and easy to misread.

-- These two are equivalent:
SELECT * FROM books LIMIT 10 OFFSET 20;
SELECT * FROM books LIMIT 20, 10;     -- offset first, then count

The second form is concise, but it bites people who expect the first number to be the count. Stick with LIMIT n OFFSET k — it's explicit and reads left to right.

OFFSET Without LIMIT: the LIMIT -1 Trick

OFFSET isn't valid on its own — SQLite's grammar requires it to follow a LIMIT. So how do you say "skip the first 10 rows and give me everything after"? The convention is LIMIT -1, which SQLite reads as "no upper bound."

CREATE TABLE numbers (n INTEGER);
INSERT INTO numbers (n) VALUES (1), (2), (3), (4), (5), (6), (7), (8);

-- Skip the first 3, return the rest
SELECT n FROM numbers ORDER BY n LIMIT -1 OFFSET 3;

Any negative LIMIT does the same thing, but -1 is the established idiom. You'll mostly see this in scripts that page through a result and want a "give me the rest" query for the final batch.

The OFFSET Performance Trap

Here's the thing nobody mentions until you hit it: OFFSET doesn't make SQLite skip work, it makes SQLite skip output. To return rows 10,001 through 10,020, the engine still walks past the first ten thousand rows internally before it starts emitting results. Small offsets are free; offsets in the tens or hundreds of thousands get noticeably slow.

For deep pagination, the standard fix is keyset pagination: instead of "skip N rows," remember the last row's sort key and ask for "rows after this one."

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    created_at TEXT,
    message TEXT
);

INSERT INTO events (created_at, message) VALUES
    ('2024-01-01 10:00', 'login'),
    ('2024-01-01 10:05', 'view'),
    ('2024-01-01 10:10', 'click'),
    ('2024-01-01 10:15', 'logout'),
    ('2024-01-01 10:20', 'login');

-- After receiving id=2, the next page is:
SELECT id, created_at, message
FROM events
WHERE id > 2
ORDER BY id
LIMIT 2;

Each page does an indexed lookup instead of scanning past everything that came before. The trade-off: you can't jump to "page 47" — only forward through the data. For infinite-scroll feeds and API cursors, that's exactly what you want.

OFFSET-based pagination is fine for admin tables and small result sets. For anything that grows without bound, reach for keyset pagination.

A Worked Example

Putting it all together — a paginated query with filtering, sorting, and a deterministic tiebreaker:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    category TEXT,
    price REAL
);

INSERT INTO products (name, category, price) VALUES
    ('Pen', 'office', 1.50),
    ('Notebook', 'office', 4.00),
    ('Stapler', 'office', 8.00),
    ('Mug', 'kitchen', 6.00),
    ('Kettle', 'kitchen', 25.00),
    ('Toaster', 'kitchen', 30.00);

-- Page 1: cheapest 2 office items, alphabetical on price ties
SELECT name, price
FROM products
WHERE category = 'office'
ORDER BY price ASC, name ASC
LIMIT 2 OFFSET 0;

Filter down to office products, sort by price ascending with name as the tiebreaker, take the first two. Change OFFSET 0 to OFFSET 2 for page 2. The query is short, but every clause earns its keep.

Next: DISTINCT

LIMIT controls how many rows come back; DISTINCT controls whether duplicates come back at all. It's the next clause in the SELECT toolkit, and it's deceptively easy to misuse — that's the next page.