SQL Tutorial · SQL Basics · Lesson 7 of 36

SQL LIMIT

Return only the first N rows, and paginate with OFFSET.

LIMIT

LIMIT n returns at most n rows. Combine it with ORDER BY to get a "top N":

Example 1Try it yourself

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

Output

name | price
4K Monitor | 329
Robot Kit | 120
Mechanical Keyboard | 89

Pagination with OFFSET

OFFSET skips rows — page 2 with 3 items per page is LIMIT 3 OFFSET 3:

Example 2Try it yourself

SELECT product_id, name FROM products ORDER BY product_id LIMIT 3 OFFSET 3;

Output

product_id | name
4 | Mechanical Keyboard
5 | Wireless Mouse
6 | 4K Monitor

For deep pages, OFFSET gets slow because the database still reads the skipped rows. Large apps use keyset pagination: WHERE id > :last_seen_id ORDER BY id LIMIT 20.

Other databases

Database	Syntax
PostgreSQL, MySQL, SQLite	`LIMIT 5 OFFSET 10`
SQL Server	`SELECT TOP 5 ...` or `OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY`
Oracle	`FETCH FIRST 5 ROWS ONLY`

Test yourself

Which clause returns page 3 of results with 10 rows per page?

LIMIT 10 OFFSET 30
LIMIT 10 OFFSET 20
LIMIT 3 OFFSET 10
LIMIT 20 OFFSET 10

Show answer

Answer: LIMIT 10 OFFSET 20