Topic 21 of 52
LIMIT / TOP
Overview
LIMIT (MySQL, PostgreSQL) and TOP (SQL Server) restrict the number of rows returned. They are essential for pagination, finding the top N records, and preventing runaway queries from returning millions of rows.
Syntax
sql
-- PostgreSQL / MySQL: LIMIT
SELECT * FROM products ORDER BY price DESC LIMIT 10;
-- OFFSET: skip rows (for pagination)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40; -- page 3
-- LIMIT with OFFSET formula
-- Page 1: LIMIT 20 OFFSET 0
-- Page 2: LIMIT 20 OFFSET 20
-- Page N: LIMIT 20 OFFSET (N-1)*20
-- SQL Server: TOP
SELECT TOP 10 * FROM products ORDER BY price DESC;
SELECT TOP 10 PERCENT * FROM products; -- top 10% of rows
-- Oracle: ROWNUM (legacy) / FETCH FIRST (modern)
SELECT * FROM products ORDER BY price DESC FETCH FIRST 10 ROWS ONLY;
-- Row number alternative (all databases)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY price DESC) AS rn FROM products
) ranked WHERE rn BETWEEN 11 AND 20;Common Pitfalls
- OFFSET-based pagination gets slower as offset increases — OFFSET 10000 LIMIT 20 scans 10020 rows to return 20.
- Always use ORDER BY with LIMIT — without it, the 'top N' results are non-deterministic and may differ each run.
- Interview tip: Cursor-based pagination (WHERE id > last_seen_id) is O(1) vs OFFSET which is O(n) — use it for large datasets.
Real-World Example
Efficient pagination for a product listing API:
example
sql
-- Cursor-based pagination (better than OFFSET for large datasets)
-- Instead of OFFSET (which gets slower as pages increase),
-- use WHERE id > last_seen_id for constant-time pagination:
-- First page
SELECT id, name, price, created_at
FROM products
WHERE is_active = TRUE
ORDER BY id ASC
LIMIT 20;
-- Next page (pass last_id from previous page)
SELECT id, name, price, created_at
FROM products
WHERE is_active = TRUE AND id > :last_id
ORDER BY id ASC
LIMIT 20;
-- Traditional OFFSET pagination (simple but slow at large offsets)
SELECT id, name, price
FROM products
WHERE is_active = TRUE
ORDER BY created_at DESC, id DESC
LIMIT :page_size OFFSET (:page_number - 1) * :page_size;
-- Top 5 products per category
SELECT DISTINCT ON (category_id) category_id, id, name, price
FROM products WHERE is_active = TRUE
ORDER BY category_id, price DESC;