Beginner-Friendly Topic
Take your time - it's perfectly normal to re-read this topic 2-3 times. Try the interactive code editor below to run code yourself. Use the Q&A section to check your understanding before moving on. You've got this! 🚀
LIMIT & OFFSET
LIMIT restricts the number of rows returned. OFFSET skips a given number of rows before returning results. Together they implement pagination - showing page 1, page 2, etc. of results. Always pair with ORDER BY for consistent results.
LIMIT & OFFSET Rules
- LIMIT n - return only the first n rows
- LIMIT n OFFSET m - skip m rows, then return n rows
- Always use ORDER BY with LIMIT - without it, result order is undefined
- Page formula: OFFSET = (page_number - 1) * page_size
- MySQL/PostgreSQL syntax: LIMIT 10 OFFSET 20
- SQL Server: SELECT TOP 10 or FETCH NEXT 10 ROWS ONLY
Pagination Examples
-- Top 2 most expensive products
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 2;
-- Result:
-- name | price
-- Laptop | 999.99
-- Phone | 599.99
-- Pagination: page 1 (first 2 products)
SELECT name, price FROM products
ORDER BY id ASC
LIMIT 2 OFFSET 0;
-- Pagination: page 2 (next 2 products)
SELECT name, price FROM products
ORDER BY id ASC
LIMIT 2 OFFSET 2;
-- Most recent orders (last 3)
SELECT id, status, created_at FROM orders
ORDER BY created_at DESC
LIMIT 3;
-- Get the single most expensive product
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 1;
-- Dynamic pagination formula:
-- page 1: LIMIT 10 OFFSET 0 (rows 1-10)
-- page 2: LIMIT 10 OFFSET 10 (rows 11-20)
-- page 3: LIMIT 10 OFFSET 20 (rows 21-30)Quick Quiz
Tip
Tip
Practice LIMIT OFFSET in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with LIMIT OFFSET is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of LIMIT OFFSET from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- LIMIT restricts the number of rows returned.
- LIMIT n - return only the first n rows
- LIMIT n OFFSET m - skip m rows, then return n rows
- Always use ORDER BY with LIMIT - without it, result order is undefined