SQL LIMIT Clause

The LIMIT clause in SQL is used to control the number of rows returned in a query result. It is particularly useful when working with large datasets, allowing you to retrieve only the required number of rows for analysis or display. Whether we’re looking to paginate results, find top records, or just display a sample of data, the LIMIT clause is an essential tool for controlling query output.

In this article, we’ll cover the basic syntax of the LIMIT clause, explain how to use it with OFFSET, and explore practical examples for common use cases.

SQL LIMIT Clause

The LIMIT clause allows you to specify the maximum number of records returned by a query. It is commonly used for limiting query results when only a subset of the data is required, such as for pagination, filtering top values, or analyzing a smaller portion of a large table.

Syntax

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

Key Terms

• offset: number of rows to skip before returning the result set.

• row_count: number of rows to return in the result set.

Examples of SQL LIMIT Clause

Let’s look at some examples of the LIMIT clause in SQL to understand it’s working. We will use the sample table name “Student” and write some LIMIT queries.

Query

CREATE TABLE student (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

INSERT INTO student (id, name, age)
VALUES (1, 'Shubham Thakur', 18),
       (2, 'Aman Chopra', 19),
       (3, 'Bhavika uppala', 20),
       (4,'Anshi Shrivastava',22);

Output

Example 1: Basic LIMIT Usage

In this example, we will only retrieve 3 rows from the student table using LIMIT.

Query

SELECT * FROM student 
LIMIT 3;

Output

Example 2: LIMIT with ORDER BY Clause

In this example, we will use the LIMIT clause with ORDER BY clause to retrieve the top 3 students sorted by their grade (assuming a Grade column exists).

Query

SELECT * FROM Student
ORDER BY Grade DESC
LIMIT 3;

Output

Explanation

The LIMIT operator can be used in situations such as the above, where we need to find the top 3 students in a class and do not want to use any conditional statements.

SQL LIMIT OFFSET

LIMIT OFFSET parameter skips a specified number of rows before returning the result set. OFFSET can only be used with the ORDER BY clause. It cannot be used on its own. OFFSET value must be greater than or equal to zero. It cannot be negative, else returns an error.

Syntax

SELECT * FROM table_name ORDER BY column_name LIMIT X OFFSET Y;

Or

SELECT * FROM table_name ORDER BY column_name LIMIT X,Y;

• X → Number of rows to return.

• Y → Number of rows to skip.

Example: Skipping First 2 Rows & Fetching 3 Rows

Query

SELECT * 
FROM Student 
ORDER BY age 
LIMIT 3 OFFSET 2;

Output

Using LIMIT to Get the nth Highest or Lowest Value

Now we will look for LIMIT use in finding highest or lowest value we need to retrieve the rows with the nth highest or lowest value. In that situation, we can use the subsequent LIMIT clause to obtain the desired outcome.

Syntax

SELECT column_list  
FROM table_name  
ORDER BY expression  
LIMIT n-1, 1;

Example: Fetching the 3rd Highest Age

Query

SELECT age FROM Student  
ORDER BY age LIMIT 2, 1;

Output

Explanation

• Orders records in descending order (highest age first).

• Skips 2 records (LIMIT 2) and retrieves the next one (LIMIT 2,1).

Using LIMIT with WHERE Clause

The WHERE clause can also be used with LIMIT. It produces the rows that matched the condition after checking the specified condition in the table.

Example: Fetching a Limited Set of Students Based on ID

Query

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

Output

Restrictions on the LIMIT clause

There are several limitations of SQL LIMIT. The following situations do not allow the LIMIT clause to be used:

• With regard to defining a view

• The use of nested SELECT statements

• Except for subqueries with table expressions specified in the FROM clause.

• Embedded SELECT statements are used as expressions in a singleton SELECT (where max = 1) within an SPL routine where embedded SELECT statements are used as expressions.

Important Points About SQL LIMIT

• The LIMIT clause is used to set an upper limit on the number of tuples returned by SQL.

• It is important to note that this clause is not supported by all SQL versions.

• The LIMIT clause can also be specified using the SQL 2008 OFFSET/FETCH FIRST clauses.

• The limit/offset expressions must be a non-negative integer.

Conclusion

The LIMIT clause is a powerful tool for optimizing query performance by restricting the number of rows retrieved. It is widely used in pagination, data sampling, and retrieving top-N records. Combining LIMIT with ORDER BY, OFFSET, and WHERE allows for more flexible and efficient data retrieval. Additionally, using LIMIT helps reduce the load on databases by fetching only the necessary data, improving query execution speed.