SQL LIMIT and OFFSET: Top N Rows and Pagination

Lesson 2.7 · Reading time: ~5 min · Practice tasks ↓

The SQL LIMIT clause restricts how many rows a query returns, and OFFSET skips a number of rows before returning the rest. In this lesson you'll learn how to get the top N rows, how to build pagination, why LIMIT needs ORDER BY, and how other databases write the same thing.

Tables often contain thousands or millions of rows, and you rarely need all of them at once. A report needs the top 10 items, an application shows 20 results per page. LIMIT and OFFSET return exactly the slice of the result you need.

The LIMIT Clause

LIMIT sets the maximum number of rows in the result. It is written at the end of the query, after ORDER BY.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT row_count;

SELECT title, length
FROM film
ORDER BY length DESC
LIMIT 5;

Result: the 5 longest films.

Why LIMIT Needs ORDER BY

Without ORDER BY, the database returns rows in any order it finds convenient, so "the first 5 rows" can be different rows every time. Always sort before limiting.

The sort must also be unique. In the query above, several films have the same length of 185 minutes, and the database decides which of them make it into the top 5. Add a column that breaks ties:

SELECT title, length
FROM film
ORDER BY length DESC, title
LIMIT 5;

Result: the 5 longest films; films of equal length are ordered by title, so the result is the same on every run.

The OFFSET Clause

OFFSET skips a given number of rows before LIMIT starts counting:

SELECT actor_id, first_name, last_name
FROM actor
ORDER BY actor_id
LIMIT 5 OFFSET 10;

Result: actors 11 to 15 by actor_id: the first 10 rows are skipped and the next 5 are returned.

MySQL and MariaDB also accept the short form LIMIT 10, 5, where the first number is the offset and the second is the row count. The explicit LIMIT 5 OFFSET 10 is easier to read and works in PostgreSQL and SQLite too.

Pagination with LIMIT and OFFSET

Pagination divides a long result into pages. For page N with S rows per page:

• LIMIT S
• OFFSET (N - 1) * S

SELECT film_id, title
FROM film
ORDER BY title, film_id
LIMIT 10 OFFSET 20;

Result: page 3 of the film list sorted by title.

The database still reads and skips all the offset rows, so very large offsets (page 10,000) become slow. For deep pagination, applications usually remember the last value of the previous page and filter with WHERE instead.

LIMIT in Other Databases

LIMIT is supported by MySQL, MariaDB, PostgreSQL and SQLite. Other databases use different syntax:

Key Takeaways:

• LIMIT n returns at most n rows.
• OFFSET n skips n rows before returning the result.
• Always use LIMIT with ORDER BY, and make the sort unique so the result is stable.
• Page N with S rows: LIMIT S OFFSET (N - 1) * S.
• SQL Server uses TOP, and the SQL standard uses FETCH FIRST n ROWS ONLY.

Frequently Asked Questions

What is the difference between LIMIT and OFFSET?

LIMIT sets how many rows to return; OFFSET sets how many rows to skip first. LIMIT 5 OFFSET 10 skips 10 rows and returns the next 5.

What is the SQL Server equivalent of LIMIT?

SELECT TOP 5 ... for the first rows, or ORDER BY ... OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY for pagination.

Why does a query with LIMIT return different rows each time?

Because the query has no ORDER BY, or the sort columns have equal values. Sort by columns that identify each row uniquely, for example add the primary key as the last sort key.