SQL LIMIT Clause

The LIMIT clause is used to restrict the number of rows returned by a query. Instead of retrieving the entire result set, LIMIT tells the database to return only a specific number of rows.

This is especially useful for large tables where retrieving all records would be unnecessary and slow. It is also commonly used to build pagination — showing results page by page in a web application.

The Reference Table

StudentID	StudentName	Age	City
1	Ravi Sharma	20	Delhi
2	Priya Mehta	22	Mumbai
3	Arjun Nair	19	Chennai
4	Sneha Kapoor	21	Pune
5	Rohit Das	23	Kolkata

Syntax

SELECT column1, column2
FROM table_name
LIMIT number;

Getting the First N Rows

SELECT * FROM Students
LIMIT 3;

Result: Only the first 3 rows are returned.

StudentID	StudentName	Age	City
1	Ravi Sharma	20	Delhi
2	Priya Mehta	22	Mumbai
3	Arjun Nair	19	Chennai

LIMIT With ORDER BY

Combining LIMIT with ORDER BY allows retrieving the top or bottom records based on a value. For example, to find the 3 youngest students:

SELECT StudentName, Age FROM Students
ORDER BY Age ASC
LIMIT 3;

Result:

StudentName	Age
Arjun Nair	19
Ravi Sharma	20
Sneha Kapoor	21

To get the oldest student, change to ORDER BY Age DESC LIMIT 1.

LIMIT With OFFSET (Pagination)

OFFSET tells the database to skip a certain number of rows before starting to return results. This is the foundation of pagination.

SELECT * FROM Students
LIMIT 2 OFFSET 2;

Explanation: Skip the first 2 rows, then return the next 2 rows.

Result:

StudentID	StudentName	Age	City
3	Arjun Nair	19	Chennai
4	Sneha Kapoor	21	Pune

Pagination Pattern

Page	Query	Rows Returned
Page 1	LIMIT 2 OFFSET 0	Rows 1–2
Page 2	LIMIT 2 OFFSET 2	Rows 3–4
Page 3	LIMIT 2 OFFSET 4	Row 5

Note on SQL Server and Oracle

The LIMIT clause is supported in MySQL and PostgreSQL. In Microsoft SQL Server, use TOP instead, and in Oracle, use FETCH FIRST n ROWS ONLY.

-- MySQL / PostgreSQL
SELECT * FROM Students LIMIT 3;

-- SQL Server
SELECT TOP 3 * FROM Students;

-- Oracle
SELECT * FROM Students FETCH FIRST 3 ROWS ONLY;

Key Points to Remember

LIMIT controls how many rows are returned — it does not filter by condition like WHERE.
Always use ORDER BY with LIMIT to ensure the returned rows are meaningful and consistent.
OFFSET skips a specified number of rows before returning results — useful for pagination.
Without ORDER BY, the "first N rows" are not guaranteed to be in any specific order.

Summary

The LIMIT clause controls the number of rows returned by a query. When combined with ORDER BY, it becomes a powerful tool for retrieving top records. With OFFSET, it enables pagination for displaying results in chunks. It is one of the most practically useful clauses in everyday SQL development.

Previous lessons

Back to courses

Next lessons