Lesson Beginner

LIMIT / OFFSET

LIMIT caps how many rows your query returns.
OFFSET tells SQL how many rows to skip before starting to return them.
Together they let you preview tables, pull "top N" results, and paginate through long lists.

LIMIT: Cap the Row Count #

While exploring a table, LIMIT is the cheapest way to see what the data looks like without waiting for every row.

sql

SELECT *
FROM orders
LIMIT 5;

LIMIT + ORDER BY: Top N #

On its own, LIMIT returns some rows, but the database can pick any rows it likes. To get the top 5 largest, smallest, newest, or highest anything, pair LIMIT with ORDER BY.

sql

-- Five largest orders
SELECT id, total
FROM orders
ORDER BY total DESC
LIMIT 5;

Warning

LIMIT without ORDER BY is non-deterministic.
Two runs of the same query can return different rows. The database picks whatever is cheapest. If the output of your query matters, always pair LIMIT with an ORDER BY clause that breaks ties uniquely.

OFFSET: Skip Rows #

OFFSET n skips the first n rows of the sorted result, then returns the next LIMIT rows.
This is how SQL handles classic pagination: page 1, page 2, page 3.

sql

-- Page 1
SELECT id, total
FROM orders
ORDER BY id
LIMIT 5
OFFSET 0;

-- Page 2 (next 5 rows)
SELECT id, total
FROM orders
ORDER BY id
LIMIT 5
OFFSET 5;

If OFFSET is larger than the number of rows available, the query returns zero rows. It is not an error.

Try It #

The seeded orders table has 12 rows. Step through them 5 at a time by changing only the OFFSET.

Loading SQL editor...

Mistakes to Watch For #

Using LIMIT without ORDER BY when the output is used downstream.
Paginating with OFFSET on a data set that is changing. Rows can shift between pages, so you may see duplicates or skip rows.
Forgetting that OFFSET counts after ORDER BY, not before.
Assuming other dialects use the same keywords. SQL Server uses TOP n, and Oracle uses FETCH FIRST n ROWS ONLY.

Practice #

Return the three largest orders by total, largest first. Show only id and total.

Loading SQL editor...

Knowledge check #

3 questions

0 / 3 answered

SELECT * FROM users LIMIT 10 returns:
For pagination (page 3 of 20-row pages), which clauses do you need?
Pagination with high OFFSET (e.g., OFFSET 1000000):

Next Step #

Continue to DISTINCT to remove duplicate rows from a result.