Overview

MYSQL Limit Clause is used to return a subset of the total rows in a table. Limit takes two numbers total results to return and offset (optional). Suppose if you say limit 20,10 than it will return 20 records after first 10 records. MYSQL Limit clause is heavily used in designing pagination features.

Syntax

Syntax for MYSQL Limit Clause is

SELECT column_name FROM table_name LIMIT NUMBER,OFFSET;

Example

Let us have a look at this with some examples

Consider table student_list which contains list of students.

+-----+------------------+--------+-----------+
| sid | name | year | hostel_id |
+-----+------------------+--------+-----------+
|   1 | Ankur Rastogi | First | 4 |
|   2 | Amit Kumar | Second | 8 |
|   3 | Ram Kumar | Second | 8 |
|   4 | Shiv Saxena | First | 6 |
|   5 | Aman Aggarwal | Third | 3 |
|   6 | Nipun Gupta | Fourth | 2 |
|   7 | Arpit Gupta | Third | 1 |
|   8 | Shantanu Rastogi | Second | 3 |
|   9 | Ramesh Kumar | Third | 1 |
|  10 | Neelima Dhingra | Second | NULL |
|  11 | Anu Sibbal | Third | NULL |
+-----+------------------+--------+-----------+

Example case 1:

Let us fetch the first 2 rows from the student _list table. Query for that will be

Select * from student_list limit 2;
+-----+---------------+--------+-----------+
| sid | name | year | hostel_id |
+-----+---------------+--------+-----------+
|   1 | Ankur Rastogi | First | 4 |
|   2 | Amit Kumar | Second | 8 |
+-----+---------------+--------+-----------+

Example Case 2:

Now let us fetch the 3rd and 4th row from the student_list. So in this case Number will be 2 and offset will also be 2 as we have to get 3rd and 4th element. MYSQL query for that will be

Select * from student_list limit 2,2;
+-----+------------------+--------+-----------+
| sid | name | year | hostel_id |
+-----+------------------+--------+-----------+
|   3 | Ram Kumar | Second | 8 |
|   4 | Shiv Saxena | First | 6 |
+-----+------------------+--------+-----------+



If you want to add more comments to the article or you see any thing incorrect please write a comment below and we will surely get back to you.



Trending Articles


Incorrect table definition there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Infix To Postfix Conversion Using Stack in Javascript

How to solve IE/Edge CORS error ?

Web Server vs Application Server Difference Explained

How to setup angular2 in visual studio ide

Binary Search Tree Insert node Implementation in Javascript

How to use ninject dependency injection in mvc

How to Add Comparison Feature in Replace in C-Sharp