How to Limit Query Results and Paginate in SQL

Posted by Kyle Hankinson June 20, 2023

---

Every database supports limiting query results, but the syntax varies. This is essential for pagination — showing results page by page.

MySQL, PostgreSQL, and SQLite: LIMIT / OFFSET

-- First 10 rows
SELECT * FROM products ORDER BY name LIMIT 10;

-- Rows 11-20 (page 2)
SELECT * FROM products ORDER BY name LIMIT 10 OFFSET 10;

-- Rows 21-30 (page 3)
SELECT * FROM products ORDER BY name LIMIT 10 OFFSET 20;

The formula: OFFSET = (page_number - 1) * page_size

SQL Server: OFFSET / FETCH NEXT (2012+)

-- First 10 rows
SELECT * FROM products ORDER BY name
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- Rows 11-20
SELECT * FROM products ORDER BY name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

ORDER BY is required when using OFFSET ... FETCH.

SQL Server: TOP (older approach)

-- First 10 rows
SELECT TOP 10 * FROM products ORDER BY name;

TOP does not support an offset directly. For pagination with TOP, you need a subquery or CTE.

Oracle: FETCH FIRST (12c+)

-- First 10 rows
SELECT * FROM products ORDER BY name
FETCH FIRST 10 ROWS ONLY;

-- Rows 11-20
SELECT * FROM products ORDER BY name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

This syntax follows the SQL:2008 standard and is identical to SQL Server's.

Oracle: ROWNUM (Legacy)

-- First 10 rows
SELECT * FROM (
    SELECT p.*, ROWNUM rn FROM products p ORDER BY name
) WHERE rn <= 10;

-- Rows 11-20
SELECT * FROM (
    SELECT p.*, ROWNUM rn FROM (
        SELECT * FROM products ORDER BY name
    ) p WHERE ROWNUM <= 20
) WHERE rn > 10;

This is verbose — prefer FETCH FIRST on Oracle 12c and above.

Quick Reference

Database	Syntax
MySQL	LIMIT 10 OFFSET 20
PostgreSQL	LIMIT 10 OFFSET 20
SQLite	LIMIT 10 OFFSET 20
SQL Server 2012+	OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Oracle 12c+	OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Oracle (legacy)	ROWNUM with nested subqueries

The Performance Problem with Large Offsets

OFFSET 100000 means the database must find and discard 100,000 rows before returning your results. This gets slower as the offset increases.

Keyset Pagination (Cursor-Based)

A faster alternative for large datasets. Instead of an offset, use the last seen value:

-- Page 1
SELECT * FROM products ORDER BY id LIMIT 10;
-- Last row has id = 10

-- Page 2 (instead of OFFSET 10)
SELECT * FROM products WHERE id > 10 ORDER BY id LIMIT 10;
-- Last row has id = 20

-- Page 3
SELECT * FROM products WHERE id > 20 ORDER BY id LIMIT 10;

This is fast regardless of how deep you paginate because it uses the index on id directly.

Trade-off: Keyset pagination does not support jumping to an arbitrary page number. It only supports "next page" and "previous page."

Getting the Total Count

For displaying "Page 3 of 47", you need the total row count alongside paginated results:

-- PostgreSQL / MySQL
SELECT COUNT(*) OVER () AS total_count, *
FROM products
ORDER BY name
LIMIT 10 OFFSET 20;

This uses a window function to return the total count with every row, avoiding a separate COUNT query.

---