🌱BeginnerFiltering & Sorting
LIMIT and OFFSET
Paginate query results using LIMIT (max rows) and OFFSET (skip rows).
Overview
LIMIT restricts how many rows are returned by a query. OFFSET skips a number of rows before returning results. Together they implement basic pagination: a "page" of results at a time. For example, page 3 with 10 rows per page would use LIMIT 10 OFFSET 20.
An important caveat: LIMIT and OFFSET are only meaningful when combined with ORDER BY. Without a stable sort, different pages might include the same row or skip rows entirely. For high-performance pagination on large tables, keyset pagination (WHERE id > last_seen_id) outperforms OFFSET-based pagination, because OFFSET requires scanning and discarding all preceding rows.
Key Points
- •LIMIT n returns at most n rows
- •OFFSET k skips the first k rows
- •Always pair with ORDER BY for predictable results
- •LIMIT ALL returns all rows (same as no LIMIT)
- •Page formula: LIMIT page_size OFFSET (page_number - 1) * page_size
- •OFFSET is slow on large tables keyset pagination is better for performance
Examples
Basic LIMIT and pagination
-- Top 5 most expensive products
SELECT * FROM products ORDER BY price DESC LIMIT 5;
-- Page 1 (first 10 rows)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;
-- Page 2 (next 10 rows)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;
-- Page 3
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;Keyset pagination (better for large tables)
-- Faster alternative to OFFSET for large tables
-- After getting rows where last id was 50:
SELECT * FROM products
WHERE id > 50
ORDER BY id
LIMIT 10;Keyset pagination skips no rows it uses the index directly.
Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4