Open In App

SQL LIMIT Clause

Last Updated : 08 May, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

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

Student Table

To create this table in your system, write the following queries:

SQL
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 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

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

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.


Like Article
Suggest improvement
Previous
Next
Share your thoughts in the comments

Similar Reads