SQL LIMIT and OFFSET: limit and paginate

“The 5 most expensive products”, “the last 10 orders”, “page 3 of the results”… All these requests rely on two keywords: LIMIT, which keeps a maximum number of rows, and OFFSET, which skips a given number of them.

Syntax

SELECT columns
FROM table
ORDER BY column
LIMIT number OFFSET rows_to_skip;

How it works

• LIMIT n keeps at most n rows.
• OFFSET k skips the first k rows before it starts keeping any.

Both clauses apply after sorting. A LIMIT without ORDER BY therefore returns arbitrary rows: for a “top 5”, sorting is essential.

Paginating results

To show pages of 10 rows, page p (starting at 1) is obtained with LIMIT 10 OFFSET (p - 1) * 10.

Depending on the database

LIMIT exists in SQLite, MySQL and PostgreSQL. SQL Server uses SELECT TOP 5 …, and standard SQL (Oracle, PostgreSQL, SQL Server) offers OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT nom, prix
FROM produits
ORDER BY prix DESC
LIMIT 5;

SELECT nom, prix
FROM produits
ORDER BY prix DESC
LIMIT 5 OFFSET 5;

SELECT id, date_commande, statut
FROM commandes
ORDER BY date_commande DESC, id DESC
LIMIT 1;

Common mistakes

SELECT nom, prix FROM produits LIMIT 5;

SELECT nom, prix FROM produits ORDER BY prix DESC LIMIT 5;

SELECT nom FROM produits ORDER BY nom LIMIT 5 OFFSET 1;

SELECT nom FROM produits ORDER BY nom LIMIT 5 OFFSET 0;

SELECT * FROM commandes ORDER BY date_commande DESC LIMIT 3;

SELECT * FROM commandes ORDER BY date_commande DESC, id DESC LIMIT 3;

Exercises

Try writing the query yourself before opening the solution.

SELECT prenom, nom, salaire FROM employes ORDER BY salaire DESC LIMIT 3;

SELECT nom, prenom FROM clients ORDER BY nom, prenom LIMIT 10 OFFSET 10;

Frequently asked questions