Introduction
Queries often return more rows than an application needs to display at once. LIMIT restricts the number of rows returned by a query, and OFFSET skips a given number of rows before starting to return results — together they enable pagination, such as showing 'page 2' of search results.
Cricket analogy: A cricket stats app showing 'page 2' of a batsman's innings list uses LIMIT to cap rows shown and OFFSET to skip the first page's entries, just like showing 'page 2' of search results.
Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1
LIMIT row_count OFFSET skip_count;
-- Example: page 3, 10 rows per page (skip first 20 rows)
SELECT *
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;Explanation
LIMIT caps the maximum number of rows returned; OFFSET tells the database how many rows to skip before it starts returning rows. Always pair LIMIT/OFFSET with ORDER BY — without a defined sort order, which rows land on which 'page' is not guaranteed to be consistent between queries. Note that LIMIT/OFFSET syntax is common in MySQL, PostgreSQL, and SQLite, but not universal: SQL Server traditionally uses SELECT TOP n, and the SQL standard defines OFFSET n ROWS FETCH NEXT m ROWS ONLY, which SQL Server, Oracle, and PostgreSQL all support.
Cricket analogy: Without ORDER BY, requesting 'page 2' of a bowler's wicket list could return different deliveries each time, like a scoreboard shuffling; ORDER BY runs, similar to SQL Server's TOP or the standard's FETCH NEXT syntax, ensures consistency.
Example
-- products table: id, name, price, category
-- Get the 5 cheapest products
SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 5;
-- SQL Server equivalent
SELECT TOP 5 name, price
FROM products
ORDER BY price ASC;Output
The query returns exactly 5 rows: the five products with the lowest price, sorted from cheapest to most expensive, e.g. 'USB Cable | 4.99', 'Notebook | 5.49', ... up to the fifth cheapest item. If fewer than 5 rows exist in the table, LIMIT simply returns all available rows.
Cricket analogy: LIMIT 5 on a price-sorted product query is like retrieving only the five lowest-scoring innings from a database, sorted ascending, stopping exactly at five regardless of how many innings exist in total.
Key Takeaways
- LIMIT caps the number of rows returned; OFFSET skips a number of rows first.
- Always combine LIMIT/OFFSET with ORDER BY for predictable, repeatable pagination.
- LIMIT/OFFSET syntax is not standard across all databases: SQL Server uses TOP or OFFSET...FETCH NEXT.
- Large OFFSET values can be slow on big tables since the database still scans and discards skipped rows.
Practice what you learned
1. What does the LIMIT clause do?
2. What does OFFSET 20 do when combined with LIMIT 10?
3. Why should LIMIT/OFFSET always be paired with ORDER BY for pagination?
4. Which clause does SQL Server traditionally use instead of LIMIT to restrict row count?
Was this page helpful?
You May Also Like
Sorting with ORDER BY
Control the order of query results using the ORDER BY clause with ascending or descending sorts.
Filtering with WHERE
Use the WHERE clause to filter rows returned by a query based on specified conditions.
SELECT Statement Basics
Learn how to retrieve data from a table using the SELECT statement, the foundation of every SQL query.