SQL Tutorial › Querying Data › LIMIT & Pagination

SQL LIMIT and Pagination

Q: How do I get the first 10 rows in SQL?

In MySQL, PostgreSQL and SQLite use ORDER BY ... LIMIT 10. In SQL Server use SELECT TOP 10, and in standard SQL FETCH FIRST 10 ROWS ONLY.

Q: How do I implement pagination in SQL?

Use ORDER BY on a unique column plus LIMIT page_size OFFSET (page - 1) page_size. For very deep pages, keyset pagination (WHERE id > last_id) is much faster.

BeginnerLesson 12 of 699 min read6 queries run on MySQL 8.4

LIMIT returns only the first N rows of a result: the five newest orders, the three most expensive products, page 2 of a list. Combined with ORDER BY it answers “top N” questions, and with OFFSET it powers pagination in almost every web app.

In this lesson

Return the first N rows with LIMIT
Skip rows with OFFSET to build pages
Find the Nth highest value
Know the equivalents in other databases and the cost of big offsets

LIMIT syntax

MySQL

SELECT columnsFROM table_nameORDER BY columnLIMIT row_count [OFFSET skip];

LIMIT is the last step of a query: MySQL builds and sorts the whole result first, then keeps the requested rows.

Sample data

products · 9 rows

product_id	product_name	category	price	stock
1	Wireless Mouse	Electronics	799	120
2	Mechanical Keyboard	Electronics	3499	45
3	USB-C Hub	Electronics	1999	0
4	Office Chair	Furniture	8999	12
5	Standing Desk	Furniture	18999	5
6	Notebook Pack	Stationery	249	300
7	Gel Pens	Stationery	199	250
8	Desk Lamp	Furniture	1499	30
9	Monitor Arm	Furniture	2799	18

Top N rows

The three most expensive products:

SQL

SELECT product_name, priceFROM productsORDER BY price DESCLIMIT 3;

ResultMySQL 8.4

product_name	price
Standing Desk	18999
Office Chair	8999
Mechanical Keyboard	3499

3 rows in set

How this query works, step by step

FROM products reads 9 rows.
ORDER BY price DESC sorts them from the highest price.
LIMIT 3 keeps the first three rows of the sorted result.

Watch out

LIMIT without ORDER BY returns some N rows, not the first or best ones. Always sort when the choice of rows matters.

OFFSET: skip rows

OFFSET skips rows before LIMIT starts counting. Products 4 to 6 by price:

SQL

SELECT product_name, priceFROM productsORDER BY price DESCLIMIT 3 OFFSET 3;

ResultMySQL 8.4

product_name	price
Monitor Arm	2799
USB-C Hub	1999
Desk Lamp	1499

3 rows in set

MySQL also accepts the short form LIMIT offset, count, so LIMIT 3, 3 means the same. The order of the two numbers is easy to mix up; LIMIT 3 OFFSET 3 is clearer.

Pagination

For page p with n rows per page: LIMIT n OFFSET (p - 1) * n. Page 2 of the employee list, 5 per page:

SQL

SELECT emp_id, first_name, last_nameFROM employeesORDER BY emp_idLIMIT 5 OFFSET 5;

ResultMySQL 8.4

emp_id	first_name	last_name
105	Vikram	Singh
106	Neha	Gupta
107	Arjun	Das
108	Kavya	Iyer
109	Rohan	Joshi

5 rows in set

How this query works, step by step

Rows are sorted by emp_id so every page is stable.
OFFSET 5 skips page 1 (employees 100 to 104).
LIMIT 5 returns employees 105 to 109.

Tip

Always sort pages by a unique column (or add one as a tie-breaker). If two rows have the same sort value, they can swap places between pages, and a user sees one row twice and misses another.

Nth highest value

A classic interview question: find the second highest salary. Sort the distinct salaries descending, skip one, take one:

SQL

SELECT DISTINCT salaryFROM employeesORDER BY salary DESCLIMIT 1 OFFSET 1;

ResultMySQL 8.4

salary
125000

1 row in set

How this query works, step by step

DISTINCT removes duplicate salaries, so a tie cannot be counted twice.
ORDER BY salary DESC puts 250,000 first.
OFFSET 1 skips it, and LIMIT 1 returns the next one: 125,000.

To get the 3rd highest, use OFFSET 2. DENSE_RANK solves the same problem and also returns who earns it.

LIMIT with ties

Which employees are the two lowest paid in Engineering?

SQL

SELECT first_name, salaryFROM employeesWHERE dept_id = 1ORDER BY salaryLIMIT 2;

ResultMySQL 8.4

first_name	salary
Amit	25000
Priya	72000

2 rows in set

The answer is Amit (25,000) and one of the two people earning 72,000. LIMIT cuts strictly after 2 rows and does not care that Priya and Karan are tied. If ties matter, use RANK().

LIMIT in other databases

Database	First 3 rows	Rows 4-6
MySQL, PostgreSQL, SQLite	`LIMIT 3`	`LIMIT 3 OFFSET 3`
SQL Server	`SELECT TOP 3 ...`	`OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY`
Oracle 12c+, standard SQL	`FETCH FIRST 3 ROWS ONLY`	`OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY`

Performance: big offsets are slow

To answer LIMIT 20 OFFSET 100000, MySQL still finds and throws away 100,000 rows. For deep pages, use keyset pagination: remember the last value of the previous page and continue after it:

SQL

SELECT emp_id, first_nameFROM employeesWHERE emp_id > 104ORDER BY emp_idLIMIT 5;

ResultMySQL 8.4

emp_id	first_name
105	Vikram
106	Neha
107	Arjun
108	Kavya
109	Rohan

5 rows in set

With an index on emp_id, MySQL jumps straight to the right place, no matter how deep the page.

Exercise: Latest three orders

Show the three most recent orders: order_id, order_date and status.

Expected resultMySQL 8.4

order_id	order_date	status
1010	2025-06-01	Delivered
1009	2025-05-15	Delivered
1008	2025-04-28	Pending

3 rows in set

Hint 1

Sort by order_date descending, then limit.

SQL

SELECT order_id, order_date, statusFROM ordersORDER BY order_date DESCLIMIT 3;

ResultMySQL 8.4

order_id	order_date	status
1010	2025-06-01	Delivered
1009	2025-05-15	Delivered
1008	2025-04-28	Pending

3 rows in set

Exercise: Third cheapest price

Find the third lowest distinct product price.

Expected resultMySQL 8.4

price
799

1 row in set

Hint 1

DISTINCT, ascending sort, skip two rows.

Quick quiz

1. What does LIMIT 5 OFFSET 10 return?

2. In MySQL, LIMIT 10, 5 means…

3. Why add ORDER BY to a query with LIMIT?

4. What is the SQL Server equivalent of LIMIT 3?

SQL Server uses TOP or OFFSET ... FETCH.

LIMIT FAQ

How do I get the first 10 rows in SQL?

In MySQL, PostgreSQL and SQLite use ORDER BY ... LIMIT 10. In SQL Server use SELECT TOP 10, and in standard SQL FETCH FIRST 10 ROWS ONLY.

How do I implement pagination in SQL?

Use ORDER BY on a unique column plus LIMIT page_size OFFSET (page - 1) * page_size. For very deep pages, keyset pagination (WHERE id > last_id) is much faster.

How do I find the second highest salary in SQL?

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; Alternatively, use DENSE_RANK() in a subquery and pick rank 2.

Does LIMIT make a query faster?

It reduces the rows sent back, and MySQL can stop early when an index provides the order. But with an ORDER BY on an unindexed column, MySQL still has to sort the whole result first.