Tutorialspoint.dev

SQL | LIMIT Clause

If there are a large number of tuples satisfying the query conditions, it might be resourceful to view only a handful of them at a time.

  • 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 specfied using the SQL 2008 OFFSET/FETCH FIRST clauses.
  • The limit/offset expressions must be a non-negative integer.

Example:
Say we have a relation, Student.
Student Table:

RollNo Name Grade
12001 Aditya 9
12002 Sahil 6
12003 Hema 8
12004 Robin 9
12005 Sita 7
12006 Anne 10
12007 Yusuf 7
12008 Alex 5

Queries

SELECT *
FROM Student
LIMIT 5;

Output:

12001 Aditya 9
12002 Sahil 6
12003 Hema 8
12004 Robin 9
12005 Sita 7
SELECT *
FROM Student
ORDER BY Grade DESC
LIMIT 3;

Output:



12006 Anne 10
12001 Aditya 9
12004 Robin 9

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 condition statements.

Using LIMIT along with OFFSET

LIMIT x OFFSET y simply means skip the first y entries and then return the next x entries.
OFFSET can only be used with 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 error.
Queries:

SELECT *
FROM Student
LIMIT 5 OFFSET 2
ORDER BY ROLLNO;

Output:

12003 Hema 8
12004 Robin 9
12005 Sita 7
12006 Anne 10
12007 Yusuf 7

Using LIMIT ALL

LIMIT ALL implies no limit.

SELECT *
FROM Student
LIMIT ALL;

The above query simply returns all the entries in the table.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.



This article is attributed to GeeksforGeeks.org

leave a comment

code

0 Comments

load comments

Subscribe to Our Newsletter