SQLite LIMIT and OFFSET

LIMIT controls how many rows a query returns. OFFSET skips a specified number of rows before returning results. Together they enable pagination — breaking large result sets into smaller, manageable pages like a search engine shows ten results per page.

Why Pagination Matters

TABLE: products  (1000 rows)
─────────────────────────────────────────────
SELECT * FROM products;     ← returns all 1000 rows

Problem: showing 1000 rows at once is slow and overwhelming.
Solution: return 10 rows per page using LIMIT and OFFSET.

Page 1: rows  1–10    LIMIT 10 OFFSET 0
Page 2: rows 11–20    LIMIT 10 OFFSET 10
Page 3: rows 21–30    LIMIT 10 OFFSET 20
Page N: rows ...      LIMIT 10 OFFSET (N-1)*10

LIMIT Syntax

SELECT column_list FROM table_name LIMIT number;

Return the Top 3 Rows

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

name           │ price
───────────────┼───────
Backpack       │ 24.99
Water Bottle   │ 8.50
Notebook       │ 2.99

LIMIT with OFFSET

-- Syntax 1 (recommended):
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 3;

-- Syntax 2 (shorthand, same result):
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3, 3;   ← means LIMIT 3 OFFSET 3

Pagination Example — 2 Items Per Page

Full list sorted by price DESC:
  1. Backpack       24.99
  2. Water Bottle    8.50
  3. Notebook        2.99
  4. Ruler           1.49
  5. Pen             0.99
  6. Eraser          0.59

PAGE 1 (LIMIT 2 OFFSET 0):
─────────────────────────────────
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 2 OFFSET 0;

name         │ price
─────────────┼───────
Backpack     │ 24.99
Water Bottle │  8.50

PAGE 2 (LIMIT 2 OFFSET 2):
─────────────────────────────────
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 2 OFFSET 2;

name     │ price
─────────┼───────
Notebook │  2.99
Ruler    │  1.49

PAGE 3 (LIMIT 2 OFFSET 4):
─────────────────────────────────
name   │ price
───────┼───────
Pen    │  0.99
Eraser │  0.59

LIMIT 1 — Fetch the Single Best Match

-- Find the most expensive product
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 1;

name     │ price
─────────┼───────
Backpack │ 24.99

OFFSET Without ORDER BY — Unpredictable Results

Always pair LIMIT and OFFSET with ORDER BY. Without ORDER BY, SQLite returns rows in undefined order. Your pages may return the same row on multiple pages or skip rows entirely.

-- WRONG: no ORDER BY makes pagination unreliable
SELECT name FROM products LIMIT 2 OFFSET 2;

-- RIGHT: ORDER BY makes pages consistent and reliable
SELECT name FROM products ORDER BY id LIMIT 2 OFFSET 2;

LIMIT in DELETE and UPDATE

SQLite allows LIMIT in DELETE and UPDATE statements to restrict how many rows are affected. This requires SQLite to be compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option.

-- Delete only the 5 oldest orders
DELETE FROM orders ORDER BY created_at ASC LIMIT 5;

Page Number to OFFSET Formula

OFFSET = (page_number - 1) * rows_per_page

Page 1:  OFFSET = (1-1) * 10 = 0
Page 2:  OFFSET = (2-1) * 10 = 10
Page 5:  OFFSET = (5-1) * 10 = 40
Page 10: OFFSET = (10-1) * 10 = 90

Key Takeaways

• LIMIT n returns at most n rows
• OFFSET n skips the first n rows before returning results
• Always combine LIMIT/OFFSET with ORDER BY for reliable pagination
• LIMIT 1 efficiently retrieves the single best or worst match
• Page offset formula: (page - 1) × rows_per_page