LIMIT Clause in SQL

The LIMIT clause in SQL is used to restrict the number of rows returned by a query. It is particularly useful when you want to retrieve only a specific number of records from a large dataset, such as the top 5 highest-paid employees or the first 10 students with the highest scores.

Key Points

1. Restricting Rows: The LIMIT clause specifies the maximum number of rows to return.
2. Offset Support: You can use the OFFSET keyword to skip a specified number of rows before starting to return rows.
3. Applicability: The LIMIT clause is commonly used with the SELECT statement.
4. Performance: Using LIMIT can improve query performance by reducing the amount of data processed and returned.

Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

• column1, column2, ...: The columns you want to retrieve.
• table_name: The table from which to retrieve data.
• number_of_rows: The maximum number of rows to return.

Syntax with OFFSET

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows OFFSET offset_value;

• offset_value: The number of rows to skip before starting to return rows.

Examples

Suppose you have a table named Employees that stores employee details.

Table: Employees

EmployeeID	Name	City	State	Salary
1	Anand	Chennai	Tamil Nadu	50000
2	Bala	Coimbatore	Tamil Nadu	60000
3	Kavitha	Karaikal	Puducherry	55000
4	Raj	Madurai	Tamil Nadu	70000
5	Kumar	Trichy	Tamil Nadu	45000
6	Ram	Salem	Tamil Nadu	65000
7	Karthik	Tirupati	Andhra Pradesh	80000
8	David	Bangalore	Karnataka	75000
9	Kannan	Kannur	Kerala	48000
10	Siva	Wayanad	Kerala	52000

To retrieve the top 5 highest-paid employees:

SELECT Name, City, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5;

Name	City	Salary
Karthik	Tirupati	80000
David	Bangalore	75000
Raj	Madurai	70000
Ram	Salem	65000
Bala	Coimbatore	60000

Using LIMIT with OFFSET

The OFFSET keyword is used to skip a specified number of rows before starting to return rows. For example, to retrieve the next 5 highest-paid employees after the top 5:

SELECT Name, City, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5 OFFSET 5;

Name	City	Salary
Kavitha	Karaikal	55000
Siva	Wayanad	52000
Anand	Chennai	50000
Kannan	Kannur	48000
Kumar	Trichy	45000

Practical Use Case

Suppose you have a table named Students that stores student details, and you want to retrieve the top 3 students with the highest percentages.

Table: Students

StudentID	Name	City	State	Percentage
1	Ram	Chennai	Tamil Nadu	92
2	Karthik	Coimbatore	Tamil Nadu	88
3	David	Bangalore	Karnataka	95
4	Kannan	Karaikal	Puducherry	91
5	Siva	Madurai	Tamil Nadu	89
6	Ramesh	Trichy	Tamil Nadu	90
7	Suresh	Salem	Tamil Nadu	87
8	Sathish	Tirupati	Andhra Pradesh	94
9	Sujatha	Palakkad	Kerala	93
10	Anand	Idukki	Kerala	85

SELECT Name, City, Percentage
FROM Students
ORDER BY Percentage DESC
LIMIT 3;

Name	City	Percentage
David	Bangalore	95
Sathish	Tirupati	94
Sujatha	Palakkad	93

Combining LIMIT with WHERE Clause

You can combine the LIMIT clause with the WHERE clause to filter and restrict the number of rows returned. For example, retrieve the top 2 students from Tamil Nadu with the highest percentages.

SELECT Name, City, Percentage
FROM Students
WHERE State = 'Tamil Nadu'
ORDER BY Percentage DESC
LIMIT 2;

Name	City	Percentage
Ram	Chennai	92
Ramesh	Trichy	90

Key Takeaways

1. The LIMIT clause restricts the number of rows returned by a query.
2. It is often used with ORDER BY to retrieve the top or bottom records.
3. The OFFSET keyword skips a specified number of rows before returning results.
4. Combining LIMIT with WHERE allows you to filter and restrict rows simultaneously.
5. Using LIMIT improves query performance by reducing the amount of data processed.