Limiting Query Results in SQL

When a query could return thousands or millions of rows, you usually only want a subset. SQL provides several clauses to restrict result size — the specific syntax depends on your database.

LIMIT (PostgreSQL, MySQL, SQLite)

LIMIT restricts the number of rows returned:

-- Return at most 10 rows
SELECT product_id, name, price
FROM products
ORDER BY price DESC
LIMIT 10;

Always pair LIMIT with an ORDER BY. Without ordering, the database returns rows in an arbitrary order — results are unpredictable and not reproducible.

TOP (SQL Server, MS Access)

SQL Server uses TOP instead of LIMIT:

-- Top 10 most expensive products
SELECT TOP 10 product_id, name, price
FROM products
ORDER BY price DESC;

-- TOP with PERCENT
SELECT TOP 5 PERCENT * FROM products ORDER BY price DESC;

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

FETCH NEXT (SQL Standard)

The ANSI SQL standard syntax — works in PostgreSQL, SQL Server 2012+, Oracle 12c+:

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

This is the most portable option across databases that follow the SQL standard.

Pagination with LIMIT and OFFSET

OFFSET skips rows before returning results. Combined with LIMIT, it enables page-by-page navigation:

-- Page 1: rows 1-20
SELECT product_id, name, price
FROM products
ORDER BY price DESC
LIMIT 20 OFFSET 0;

-- Page 2: rows 21-40
LIMIT 20 OFFSET 20;

-- Page N formula: OFFSET = (page_number - 1) * page_size
-- Page 5: rows 81-100
LIMIT 20 OFFSET 80;

OFFSET Pagination Performance Problem

OFFSET-based pagination degrades as page numbers grow. To reach page 500 with LIMIT 20 OFFSET 9980, the database reads and discards 9,980 rows before returning your 20. For large datasets, this is slow.

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;

-- Next page: use the last row's values as a cursor
-- (previous last row had price=49.99, product_id=8821)
SELECT product_id, name, price
FROM products
WHERE (price, product_id) < (49.99, 8821)
ORDER BY price DESC, product_id DESC
LIMIT 20;

Cursor pagination is O(log n) with a proper index regardless of page depth. The trade-off: you can only navigate forward/backward, not jump to arbitrary pages.

Top-N Per Group

A common requirement: the top N rows within each category. LIMIT alone can’t do this — use a window function:

-- Top 3 products by sales within each category
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;

Checking for Existence

When you only need to know if at least one matching row exists, EXISTS is cleaner and often faster than LIMIT 1:

-- Does this product have active orders?
SELECT EXISTS (
    SELECT 1 FROM orders WHERE product_id = 8821 AND status = 'active'
);

The optimizer can stop at the first match without fetching any column data.

Practical Examples

Most recent 5 orders:

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

Random sample (PostgreSQL):

-- Fast approximate: use TABLESAMPLE
SELECT * FROM large_table TABLESAMPLE BERNOULLI(1);  -- ~1% sample

-- Exact but slower
SELECT * FROM products ORDER BY RANDOM() LIMIT 100;

Pagination with total count in one query:

SELECT
    product_id,
    name,
    price,
    COUNT(*) OVER () AS total_count
FROM products
ORDER BY price DESC
LIMIT 20 OFFSET 0;

COUNT(*) OVER () is a window function that adds the total matching row count to every returned row — one round-trip instead of two.