SQLite LIMIT and OFFSET: Return the First N Rows (Tested)

Ninad Pathak

October 7, 2026

How do you get only the first N rows from an SQLite query? With LIMIT; SQL Server’s TOP is a syntax error here. OFFSET skips rows, which is how pagination works. In this article I’ll walk you through LIMIT and OFFSET for top-N queries and pagination, plus a faster way to page through big tables.

TLDR

SELECT … LIMIT 10 returns no more than 10 rows.
Add ORDER BY first, or “the first 10 rows” can be any 10 rows.

LIMIT 10 OFFSET 20 skips 20 rows and returns the next 10. LIMIT 20, 10 means the same, with the numbers swapped.
SQLite has no TOP. Use ORDER BY … LIMIT n instead.
LIMIT -1 means no limit, which lets you use OFFSET on its own.

What is LIMIT in SQLite?

LIMIT caps how many rows a SELECT returns. SQLite’s docs say “the SELECT returns the first N rows of its result set only, where N is the value that the LIMIT expression evaluates to” (SQLite SELECT). OFFSET skips rows before that count starts. Both apply after the rest of the query, including ORDER BY.

SQLite LIMIT syntax

SELECT columns FROM table_name
[WHERE …] [ORDER BY …]
LIMIT row_count [OFFSET skip_count];

-- the same thing, comma form (skip first, then count):
LIMIT skip_count, row_count;

How to return the first N rows

To show you how this works, I will use a posts table with 12 rows and a views count on each:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  views INTEGER NOT NULL
);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 12)
INSERT INTO posts (title, views) SELECT 'Post ' || i, (i * 37) % 100 FROM n;

sqlite> SELECT count(*) AS posts, min(id), max(id) FROM posts;
posts  min(id)  max(id)
-----  -------  -------
12     1        12

Step 1: Limit the number of rows

sqlite> SELECT id, title FROM posts LIMIT 3;
id  title
--  ------
1   Post 1
2   Post 2
3   Post 3

Without ORDER BY, which rows you get isn’t defined. Here they came back in id order, but you shouldn’t rely on that.

Step 2: Sort first to get the top N

ORDER BY with LIMIT is how you write “top 3” in SQLite:

sqlite> SELECT id, title, views FROM posts ORDER BY views DESC LIMIT 3;
id  title   views
--  ------  -----
8   Post 8  96
5   Post 5  85
2   Post 2  74

Step 3: Skip rows with OFFSET

OFFSET 3 skips the first three rows of the sorted result:

sqlite> SELECT id, title FROM posts ORDER BY id LIMIT 3 OFFSET 3;
id  title
--  ------
4   Post 4
5   Post 5
6   Post 6

Examples

These examples cover the variations and traps you’ll meet once the basics work.

The comma form swaps the numbers

In LIMIT 3, 2, the first number is the offset and the second is the count. The docs note that this “is counter-intuitive” (SQLite SELECT). It returns two rows after skipping three:

sqlite> SELECT id, title FROM posts ORDER BY id LIMIT 3, 2;
id  title
--  ------
4   Post 4
5   Post 5

Prefer LIMIT … OFFSET …, which reads in the right order.

SQLite has no SELECT TOP

TOP is SQL Server syntax, and SQLite rejects it:

sqlite> SELECT TOP 3 * FROM posts;
Error: in prepare, near "3": syntax error
  SELECT TOP 3 * FROM posts;
             ^--- error here

OFFSET without a limit

OFFSET needs a LIMIT in front of it. “If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned” (SQLite SELECT), so LIMIT -1 OFFSET 10 returns everything after the first ten rows:

sqlite> SELECT count(*) AS rows_after_skipping_10 FROM (SELECT id FROM posts ORDER BY id LIMIT -1 OFFSET 10);
rows_after_skipping_10
----------------------
2

Paginate from Python with parameters

LIMIT and OFFSET accept bound parameters, so page numbers never go into the SQL string:

import sqlite3

con = sqlite3.connect("blog.db")
page, per_page = 2, 5
rows = con.execute(
    "SELECT id, title FROM posts ORDER BY id LIMIT ? OFFSET ?",
    (per_page, (page - 1) * per_page),
).fetchall()
print(f"page {page}:", rows)

$ python3 page.py
page 2: [(6, 'Post 6'), (7, 'Post 7'), (8, 'Post 8'), (9, 'Post 9'), (10, 'Post 10')]

Page through a large table with a WHERE instead of OFFSET

OFFSET skips rows of the finished result, so SQLite still produces every skipped row before it returns the ones you want. Deep pages get slower as the offset grows. Keyset pagination remembers the last id you showed and starts after it. The primary key finds that spot directly:

sqlite> SELECT id, title FROM posts WHERE id > 10 ORDER BY id LIMIT 5;
id  title
--  -------
11  Post 11
12  Post 12

Use LIMIT in a subquery

A LIMIT inside a subquery picks one value. Here, LIMIT 1 OFFSET 2 finds the third-highest view count, and the outer query returns the posts above it:

sqlite> SELECT title, views FROM posts WHERE views > (SELECT views FROM posts ORDER BY views DESC LIMIT 1 OFFSET 2) ORDER BY views DESC;
title   views
------  -----
Post 8  96
Post 5  85

DELETE … LIMIT only works in some builds

DELETE and UPDATE accept ORDER BY and LIMIT only when SQLite was compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT (SQLite DELETE). The sqlite3 shell that ships with macOS has it, and Homebrew’s build doesn’t:

sqlite> DELETE FROM posts ORDER BY views LIMIT 2;
sqlite> SELECT count(*) AS posts_left FROM posts;
posts_left
----------
10
sqlite> SELECT sqlite_compileoption_used('ENABLE_UPDATE_DELETE_LIMIT') AS delete_limit_compiled_in;
delete_limit_compiled_in
------------------------
1
$ /opt/homebrew/opt/sqlite/bin/sqlite3 blog.db "DELETE FROM posts ORDER BY views LIMIT 2;"
Parse error in 2nd command line argument: near "ORDER": syntax error
  DELETE FROM posts ORDER BY views LIMIT 2;
                    ^--- error here

For portable code, delete by key instead: DELETE FROM posts WHERE id IN (SELECT id FROM posts ORDER BY views LIMIT 2);.

Looking for SQLite’s size limits?

If you searched for “SQLite limits” and meant capacity rather than the LIMIT clause: the theoretical maximum is 2^64 rows per table, and the maximum database size is about 281 TB with 64 KiB pages (Limits in SQLite). SQLite’s docs add that a database of that size “can hold no more than approximately 2e+13 rows”.

Conclusion

Use ORDER BY … LIMIT n for top-N queries, and LIMIT … OFFSET … for simple pagination. For deep pages in large tables, switch to keyset pagination with WHERE id > last_id.