LIMIT & OFFSET
In this page:
Limiting Your Results
Sometimes your queries match thousands of rows, but you only need to show a handful at a time. We can use the LIMIT clause to cap the maximum number of rows returned. This keeps our queries fast and manageable, especially on large tables.
Example: Limiting Your Results
SELECT * FROM products LIMIT 5;Skipping Records with OFFSET
The OFFSET clause tells MySQL to skip a specific number of rows before it starts returning results, such as skipping the first 20 rows to show page two. It is always used together with the LIMIT clause to define both where to start and how many rows to return.
Example: Skipping Records with OFFSET
SELECT * FROM products LIMIT 20 OFFSET 20;The Short Hand Limit Syntax
MySQL has a shorthand way to write both LIMIT and OFFSET in one line. You write LIMIT, then the offset number, a comma, and finally the row count limit, e.g. LIMIT 20, 10 skips 20 rows and returns the next 10.
Example: The Short Hand Limit Syntax
SELECT * FROM products LIMIT 20, 20;Creating Pages of Results
LIMIT and OFFSET are the keys to building database pagination for things like search results or product listings. By changing the offset based on the page number, we can display data in structured pages without loading the entire table at once.
Example: Creating Pages of Results
SELECT * FROM products LIMIT 10 OFFSET 20; -- page 3, 10 per pageMixing Filters Sorts and Limits
You can combine WHERE, ORDER BY, and LIMIT in a single query to filter, sort, and cap results together. The rules of order are strict: write WHERE first, then ORDER BY, and place LIMIT at the very end, or MySQL will raise a syntax error.
Example: Mixing Filters Sorts and Limits
SELECT * FROM products WHERE category = 'Books' ORDER BY price DESC LIMIT 5;Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: