The SQL LIMIT clause is used to restrict the number of rows returned by a query. It is particularly useful for pagination and when working with large datasets, as it allows you to fetch only a subset of records.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT [offset,] row_count;

• offset: Number of rows to skip before starting to return rows.
• row_count: Number of rows to return.

Examples

1. Basic LIMIT Usage Retrieve the first 3 rows from the student table:

   SELECT * FROM student LIMIT 3;

Output: Displays the first 3 rows of the student table.

2. LIMIT with ORDER BY Retrieve the top 3 students ordered by their grades in descending order:

   SELECT * FROM Student ORDER BY Grade DESC LIMIT 3;

Output: Shows the top 3 students based on the Grade.

3. LIMIT with OFFSET Skip the first 2 rows and then return the next 5 rows:

   SELECT * FROM Student ORDER BY ROLLNO LIMIT 5 OFFSET 2;

or

   SELECT * FROM Student ORDER BY ROLLNO LIMIT 2, 5;

Output: Displays rows 3 through 7 of the Student table.

4. LIMIT for nth Highest or Lowest Value Retrieve the second highest age from the Student table:

   SELECT age FROM Student ORDER BY age LIMIT 1 OFFSET 1;

Output: Shows the second highest age.

5. LIMIT with WHERE Clause Retrieve the age of students with id less than 4, sorted by age, and return only the second record:

   SELECT age FROM Student WHERE id < 4 ORDER BY age LIMIT 1 OFFSET 1;

Output: Shows the second lowest age among students with id less than 4.

Restrictions

• The LIMIT clause cannot be used in certain scenarios, such as defining a view or within nested SELECT statements.
• Not all SQL databases support the LIMIT clause. Some use alternatives like OFFSET/FETCH in SQL Server.

Important Points

• The LIMIT clause sets an upper limit on the number of rows returned.
• Use OFFSET to skip a number of rows before starting to return the results.
• The expressions for LIMIT and OFFSET must be non-negative integers.