DocsFundamentalsLIMIT & OFFSET

LIMIT & OFFSET

Fundamentals

Concept

LIMIT restricts the number of rows returned by a query, and OFFSET skips a specified number of rows before starting to return results. Together they enable pagination — showing results page by page — which is essential for any user-facing application that deals with large datasets.

LIMIT is applied after ORDER BY, so you can reliably fetch the top-N or bottom-N rows. OFFSET tells the database how many rows to skip. For example, page 3 with 20 rows per page uses LIMIT 20 OFFSET 40. Note that the SQL standard uses FETCH FIRST N ROWS ONLY as an alternative syntax, but LIMIT/OFFSET is universally supported.

While convenient, OFFSET-based pagination has a performance drawback: the database must still read and discard all the offset rows. For deep pagination (e.g., page 5000) consider keyset pagination — filtering by the last-seen value of the ORDER BY column — which is much more efficient.

Syntax

-- Return only 10 rows
SELECT * FROM products
ORDER BY price DESC
LIMIT 10;

-- Skip first 20 rows, return next 10 (page 3)
SELECT * FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 20;

-- SQL standard alternative
SELECT * FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Page 2 of orders (10 per page), most recent first
SELECT
  order_id,
  customer_id,
  total_amount,
  order_date
FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 10;

-- Keyset pagination (more efficient for deep pages)
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date < '2024-06-15'  -- last seen value
ORDER BY order_date DESC
LIMIT 10;

Common Pitfalls & Tips

1Always use ORDER BY with LIMIT. Without it the rows you get back are non-deterministic and may change between executions.
2Large OFFSET values degrade performance because the database still processes all skipped rows. Use keyset/cursor pagination for deep pages.
3If two rows share the same ORDER BY value, LIMIT may return different subsets between runs. Add a tiebreaker column (e.g., primary key) to make results deterministic.

Practice LIMIT & OFFSET