SqlDatabaseBeginner

LIMIT / OFFSET

Limits number of rows returned and optionally skips rows (paging).

Review the syntaxStudy the examplesOpen the coding app
SELECT columns FROM table_name ORDER BY column DESC LIMIT n OFFSET m;
Open Coding Reference

This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.

What it does

Overview

Limits number of rows returned and optionally skips rows (paging).

The LIMIT and OFFSET clauses are used to implement pagination by restricting the result set size and skipping a specified number of rows. While LIMIT specifies the maximum number of rows to return, OFFSET determines the starting point in the sorted result set. Technically, OFFSET results in a performance bottleneck on large datasets because the database engine must still fetch and process all skipped rows (the offset count) before discarding them and returning the actual limit. This results in O(N) complexity where N is the offset value. To ensure deterministic results, these clauses must be used with an ORDER BY statement; without it, the order of returned rows is undefined and can change between executions. For high-performance scenarios, especially with millions of rows, keyset pagination (also known as the 'seek method') is preferred over OFFSET. Keyset pagination uses a WHERE filter on a unique, indexed column (like a primary key or timestamp) to start scanning from the last seen value, maintaining O(log N) or O(1) performance regardless of the depth of the page.

Quick reference

Syntax

SELECT columns FROM table_name ORDER BY column DESC LIMIT n OFFSET m;

Inputs

Parameters

nMaximum number of rows to return.
m (optional)Number of rows to skip (OFFSET).

See it in practice

Examples

1

Basic Pagination (Fetching the First Page)

SELECT product_id, product_name, price
FROM products
ORDER BY created_at DESC
LIMIT 10;
Output:
10 rows returned starting from the newest product

Retrieves the first 10 records. Always use ORDER BY to ensure that the 'first' records are consistent.

2

Using OFFSET for Subsequent Pages

SELECT user_id, email
FROM users
ORDER BY user_id ASC
LIMIT 20 OFFSET 40;
Output:
20 rows returned (rows 41 to 60)

Skips the first 40 rows and returns the next 20. This is typically used for 'Page 3' logic where the page size is 20.

3

Optimized Keyset Pagination (Seek Method)

SELECT id, title, published_at
FROM articles
WHERE published_at < '2023-10-27T10:00:00Z'
ORDER BY published_at DESC
LIMIT 5;
Output:
5 rows starting immediately after the provided timestamp

Instead of OFFSET, we filter by the last value seen on the previous page. This avoids the performance penalty of scanning skipped rows. For reliable ordering, ISO 8601 strings should be used for date comparisons.

Debug faster

Common Errors

1

LogicError

Cause: Using LIMIT and OFFSET without an ORDER BY clause.

Fix: Always include a unique ORDER BY column to ensure the database returns rows in a consistent, predictable sequence.

SELECT * FROM logs LIMIT 10 OFFSET 50; -- Bad: Order is not guaranteed
2

PerformanceError

Cause: Applying large OFFSET values on tables with millions of rows.

Fix: Switch to keyset pagination (WHERE column > last_value) to leverage indexes and avoid scanning skipped rows.

SELECT * FROM transactions ORDER BY id LIMIT 10 OFFSET 1000000; -- Very slow

Runtime support

Compatibility

PostgreSQL/MySQL/SQLite support LIMIT; SQL Server uses OFFSET/FETCHVaries by DB

Check DB-specific paging syntax

Source: MDN Web Docs

Common questions

Frequently Asked Questions

Limits number of rows returned and optionally skips rows (paging).

n: Maximum number of rows to return. m: Number of rows to skip (OFFSET).

LogicError: Always include a unique ORDER BY column to ensure the database returns rows in a consistent, predictable sequence. PerformanceError: Switch to keyset pagination (WHERE column > alue) to leverage indexes and avoid scanning skipped rows.