SQL LIMIT Clause
The LIMIT clause in SQL allows users to control the amount of data retrieved and displayed in the result set.
It is useful when only a subset of records is needed for analysis or display purposes in large databases with thousands of records.
Syntax
The Syntax to use LIMIT in SQL is:
SELECT column1, column2, …
FROM table_name
WHERE condition
ORDER BY column
LIMIT [offset,] row_count;
Here,
- offset: number of rows to skip before returning the result set.
- row_count: number of rows to return in the result set.
SQL LIMIT Examples
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.
Student Table
To create this table in your system, write the following queries:
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);
LIMIT Clause Example
In this example, we will only retrieve 3 rows from the student table using LIMIT.
Query:
SELECT * FROM student
LIMIT 3;
Output:
LIMIT Clause Example
LIMIT with ORDER BY Clause
In this example, we will use the LIMIT clause with ORDER BY clause
Query:
SELECT * FROM Student
ORDER BY Grade DESC
LIMIT 3;
Output:
LIMIT with ORDER BY Clause
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;
The first value X is the offset value and the second value Y is the LIMIT value.
SQL LIMIT OFFSET Example
In this example, we will skip first 2 values using offset and print only 3 rows.
Query:
SELECT * FROM Student
ORDER BY ROLLNO LIMIT 2,5;
Output:
SQL LIMIT OFFSET Example Output
SQL 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
In this example, we will retrieve second highest age of student table.
Query:
SELECT age FROM Student
ORDER BY age LIMIT 2, 1;
Output:
SQL LIMIT to Get the nth Highest Value Example Output
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
In this example, we will use SQL LIMIT with WHERE Clause
Query:
SELECT age
FROM Student
WHERE id<4
ORDER BY age
LIMIT 2, 1;
Output:
LIMIT with WHERE Clause Example 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.