LIMIT & OFFSET
Beginner⏱️ 5 mins read
What You'll Learn
LIMIT restricts the number of rows returned. OFFSET skips rows — essential for pagination. Always combine with ORDER BY when paginating to ensure consistent results. SQL Server uses TOP instead of LIMIT. Oracle uses FETCH FIRST n ROWS ONLY.
Syntax
SELECT cols FROM table LIMIT n;
SELECT cols FROM table LIMIT n OFFSET k;Example
-- First 10 rows
SELECT * FROM products LIMIT 10;
-- Page 3 of results (10 per page)
SELECT * FROM products
ORDER BY name
LIMIT 10 OFFSET 20;
-- Top 5 most expensive
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 5;Common Mistakes
Using LIMIT/OFFSET without ORDER BY for pagination. Without ORDER BY the 'page' is non-deterministic — you may see duplicate or missing rows across pages.
Interview Tips
For very large offsets (OFFSET 10000000), performance degrades because the DB still scans all skipped rows. Mention keyset pagination (WHERE id > last_seen_id) as a better alternative for large datasets.
Practice
Implement page 4 of customers (10 per page), ordered alphabetically by last name.