SQL LIMIT & OFFSET – Pagination

LIMIT Syntax

LIMIT n tells the database to return at most n rows. It is placed at the very end of a SELECT statement — after ORDER BY. Without ORDER BY, LIMIT returns an arbitrary subset of matching rows.

SQL

-- Return the 5 highest-paid employees
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Return any 10 rows from the products table (unspecified order)
SELECT * FROM products LIMIT 10;

-- LIMIT with WHERE: first 3 Engineering employees alphabetically
SELECT first_name, department
FROM employees
WHERE department = 'Engineering'
ORDER BY first_name ASC
LIMIT 3;

ℹ️

Always pair LIMIT with ORDER BY for meaningful results

Without ORDER BY, the rows returned by LIMIT are non-deterministic — the database picks whichever rows it processes first, which can change between query runs. When you want the "top 10 by salary" or "latest 5 orders", always specify ORDER BY to make the selection meaningful and reproducible.

OFFSET – Skipping Rows for Pagination

OFFSET n skips the first n rows of the result set before applying LIMIT. Used together, LIMIT and OFFSET divide a large result set into pages.

SQL

-- Page 1: rows 1-10 (skip 0, take 10)
SELECT id, title, price
FROM products
ORDER BY id ASC
LIMIT 10 OFFSET 0;

-- Page 2: rows 11-20 (skip 10, take 10)
SELECT id, title, price
FROM products
ORDER BY id ASC
LIMIT 10 OFFSET 10;

-- Page 3: rows 21-30 (skip 20, take 10)
SELECT id, title, price
FROM products
ORDER BY id ASC
LIMIT 10 OFFSET 20;

The general pagination formula:

SQL – Pagination formula

-- OFFSET = (page_number - 1) * page_size
-- page_number starts at 1

-- Page 5 of 10 items per page:
-- OFFSET = (5 - 1) * 10 = 40
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 40;

-- In your application code (Python example):
-- page = 5
-- page_size = 10
-- offset = (page - 1) * page_size  → 40
-- query = f"SELECT * FROM products ORDER BY id LIMIT {page_size} OFFSET {offset}"

Top-N Pattern

LIMIT combined with ORDER BY is the standard SQL pattern for retrieving the "top N" rows by any metric — highest salaries, most recent orders, bestselling products:

SQL

-- Top 3 highest-paid employees
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

-- 5 most recently placed orders
SELECT id, customer_id, total, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 5;

-- Cheapest product in the Electronics category
SELECT name, price
FROM products
WHERE category = 'Electronics'
ORDER BY price ASC
LIMIT 1;

-- 10 best-reviewed products (by average rating)
SELECT product_id, AVG(rating) AS avg_rating
FROM reviews
GROUP BY product_id
ORDER BY avg_rating DESC
LIMIT 10;

SQL Server and Oracle Syntax

MySQL, PostgreSQL, and SQLite use LIMIT / OFFSET. SQL Server uses TOP for simple limits and the SQL standard FETCH FIRST syntax for pagination. Oracle uses FETCH FIRST or the legacy ROWNUM.

SQL – SQL Server (T-SQL)

-- SQL Server: TOP N (simple limit — no offset)
SELECT TOP 5 first_name, salary
FROM employees
ORDER BY salary DESC;

-- SQL Server: FETCH FIRST (standard SQL:2008 — supports pagination)
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;  -- page 2 of 10 rows

SQL – PostgreSQL / standard SQL

-- PostgreSQL supports both LIMIT/OFFSET and the standard FETCH FIRST
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;          -- equivalent to LIMIT 5

-- With offset (standard SQL)
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;  -- skip 10, take 5

Performance Note: Large OFFSET Is Slow

A critical performance fact: OFFSET n does not skip rows efficiently — the database must still read and discard the first n rows before returning the requested page. OFFSET 100000 forces the database to read 100,000 rows just to throw them away.

⚠️

Large OFFSET degrades linearly

Page 1 (OFFSET 0) is instant. Page 100 (OFFSET 990 with 10 rows/page) is fast. Page 10,000 (OFFSET 99,990) requires the database to scan 100,000 rows every time. For user-facing pagination on large tables, switch to cursor-based pagination instead.

SQL – Cursor-based pagination (fast)

-- Offset-based (slow on large tables):
SELECT id, title FROM articles ORDER BY id DESC LIMIT 10 OFFSET 100000;

-- Cursor-based (fast even on millions of rows):
-- First page:
SELECT id, title FROM articles ORDER BY id DESC LIMIT 10;
-- Subsequent pages: pass the last seen id as the cursor
-- If last page ended at id = 4532:
SELECT id, title
FROM articles
WHERE id < 4532          -- cursor: start after the last seen row
ORDER BY id DESC
LIMIT 10;

Cursor-based pagination uses a WHERE condition on an indexed column instead of OFFSET — the database uses the index to jump directly to the starting point. This runs in constant time regardless of how deep into the result set you are.

📋 Summary

LIMIT n — return at most n rows. Always pair with ORDER BY for deterministic results.
LIMIT n OFFSET m — skip m rows then return n. Pagination formula: OFFSET = (page - 1) × page_size.
The Top-N pattern uses ORDER BY column DESC LIMIT n to retrieve the highest-ranked rows efficiently.
SQL Server uses TOP n for simple limits and OFFSET n ROWS FETCH NEXT n ROWS ONLY for pagination.
Large OFFSETs are slow — the database must read and discard all skipped rows. Use cursor-based pagination (keyset pagination) on large tables for constant-time page fetches.

FAQ

What happens if LIMIT is larger than the number of matching rows? +

The database simply returns all matching rows — there is no error. If you say LIMIT 100 but only 42 rows match, you get 42 rows. LIMIT is a maximum, not a requirement.

Is LIMIT supported by all databases? +

LIMIT is supported by MySQL, PostgreSQL, SQLite, and MariaDB. SQL Server and older Oracle versions use different syntax: TOP n (SQL Server), ROWNUM <= n (Oracle pre-12c), or the standard FETCH FIRST n ROWS ONLY (SQL Server 2012+, Oracle 12c+, PostgreSQL). If you are writing portable SQL that must run on SQL Server, use the FETCH FIRST syntax since it is standard SQL.

How do I count the total number of pages for pagination? +

Run a separate COUNT query before or alongside your paginated query: SELECT COUNT(*) FROM products WHERE category = 'Electronics';. Then divide by page_size and round up: total_pages = CEIL(total_rows / page_size). In most application frameworks, this count query is run once and cached to avoid running it on every page navigation.

Can I use LIMIT without OFFSET? +

Yes — LIMIT and OFFSET are independent. LIMIT 5 without OFFSET returns the first 5 rows (OFFSET defaults to 0). OFFSET without LIMIT is also valid — it skips rows but returns all remaining rows. In practice, LIMIT without OFFSET is the common case for Top-N queries. OFFSET without LIMIT is rarely used — the main use case is skipping a fixed number of header rows in certain reporting queries.