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 specified 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 conditional 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 ORDER BY ROLLNO LIMIT 5 OFFSET 2; or SELECT * FROM Student ORDER BY ROLLNO LIMIT 2,5; # it skips the first 2 values and then return the next 5 entries
First query and second query return same results. In the second query limit is followed by two values. LIMIT X,Y the first value X is the offset value (skips X number of entries) and the second value Y is the limit (it returns the next Y number of entries).
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.
This article is contributed by Anannya Uberoi. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to review-team@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above.
Please Login to comment...