SQL LIMIT, OFFSET, and TOP

When a query could return thousands of rows, you often need only a subset. SQL provides several clauses for this — the syntax depends on which database you’re using.

LIMIT (PostgreSQL, MySQL, SQLite)

LIMIT restricts how many rows are returned:

-- Top 10 by price
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 10;

-- First 50 records
SELECT * FROM customers LIMIT 50;

Always use ORDER BY with LIMIT. Without it, which rows you get is undefined.

TOP (SQL Server, MS Access)

-- SQL Server: top 10
SELECT TOP 10 name, price FROM products ORDER BY price DESC;

-- Top 5% of rows by row count
SELECT TOP 5 PERCENT * FROM products ORDER BY price DESC;

-- WITH TIES: include rows tied for the last rank position
SELECT TOP 10 WITH TIES name, price FROM products ORDER BY price DESC;

FETCH NEXT (SQL Standard)

The ANSI SQL standard clause — supported in PostgreSQL, SQL Server 2012+, Oracle 12c+:

SELECT name, price FROM products
ORDER BY price DESC
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

OFFSET: Skip Rows

OFFSET combined with LIMIT is the classic pagination pattern:

-- Page 1 (rows 1-20)
SELECT * FROM products ORDER BY name LIMIT 20 OFFSET 0;

-- Page 2 (rows 21-40)
SELECT * FROM products ORDER BY name LIMIT 20 OFFSET 20;

-- Page 3 (rows 41-60)
SELECT * FROM products ORDER BY name LIMIT 20 OFFSET 40;

-- Formula: OFFSET = (page_number - 1) * page_size

OFFSET Performance Problem

OFFSET-based pagination degrades as page numbers grow. To get page 500 at 20 rows per page (OFFSET 9980), the database reads and discards 9,980 rows before returning your 20. This is an O(n) operation.

Cursor-based pagination (keyset pagination) solves this:

-- First page
SELECT product_id, name, price
FROM products
ORDER BY price DESC, product_id DESC
LIMIT 20;
-- Save last row: price=149.99, product_id=8821

-- Next page: use last row's values as a cursor
SELECT product_id, name, price
FROM products
WHERE (price, product_id) < (149.99, 8821)  -- PostgreSQL row comparison
ORDER BY price DESC, product_id DESC
LIMIT 20;

Cursor pagination is O(log n) with an index regardless of how deep you go. The trade-off: you can navigate forward/backward, not jump to arbitrary pages.

Top-N Per Group

LIMIT alone can’t return top N per category — you need a window function:

-- Top 3 products per category by sales
WITH ranked AS (
    SELECT
        product_id,
        name,
        category,
        sales_count,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_count DESC) AS rn
    FROM products
)
SELECT product_id, name, category, sales_count
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

Find the Nth Row

-- Second highest price (skip 1, take 1)
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 1;

-- Third most recent order
SELECT order_id, created_at FROM orders
ORDER BY created_at DESC
LIMIT 1 OFFSET 2;

Pagination Query with Total Count

A common API pattern: return one page AND the total count in a single query:

SELECT
    product_id,
    name,
    price,
    COUNT(*) OVER () AS total_count   -- count without collapsing rows
FROM products
WHERE is_active = TRUE
ORDER BY name
LIMIT 20 OFFSET 0;

COUNT(*) OVER () is a window function that adds the total result count to every row, so you know how many pages exist without a second query.

Database Syntax Reference

Database           | Clause
───────────────────────────────────────────────────────────
PostgreSQL         | LIMIT n OFFSET m
MySQL              | LIMIT n OFFSET m  (also: LIMIT m, n)
SQLite             | LIMIT n OFFSET m
SQL Server         | TOP n / OFFSET m ROWS FETCH NEXT n ROWS ONLY
Oracle (12c+)      | OFFSET m ROWS FETCH NEXT n ROWS ONLY
Oracle (older)     | WHERE ROWNUM <= n (in subquery)
DB2                | FETCH FIRST n ROWS ONLY

Practical Examples

Most recent 5 customer orders:

SELECT order_id, amount, created_at
FROM orders
WHERE customer_id = 1042
ORDER BY created_at DESC
LIMIT 5;

Products on page 3 of a catalog (20 per page):

SELECT product_id, name, price
FROM products
WHERE is_active = TRUE
ORDER BY name
LIMIT 20 OFFSET 40;  -- page 3: skip first 40 rows

Sample 100 random records for testing:

-- PostgreSQL
SELECT * FROM customers ORDER BY RANDOM() LIMIT 100;

-- Faster approximate sample
SELECT * FROM customers TABLESAMPLE BERNOULLI(1);  -- ~1%