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

To retrieve the top 5 highest-paid employees:

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

​

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;

​

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

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

​

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;

​

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.