MySQL LIMIT
The MySQL LIMIT clause is used to narrow or limit, a result set to the specified number of rows.
The LIMIT clause accepts one or two arguments, one is offset and another one is count.
Offset: It is used to specify the offset of the first row to be returned.
Count: It is used to specify the maximum number of rows to be returned.
Syntax:
|
1 |
SELECT column_name(s) FROM table_name LIMIT offset_number, count; |
SELECT * FROM table_name LIMIT offset,count;
|
1 |
SELECT * FROM table_name LIMIT 10; |
The query above will limit the result set to 10 rows.
When we will use argument in the limit clause, this argument will be used to return the maximum number of rows to be returned from the beginning of the result set.
|
1 |
SELECT column_name FROM table_name LIMIT count; |
The above ex equivalent to this ex with the LIMIT clause that accepts two arguments.
|
1 |
SELECT column_name FROM table_name LIMIT 0, count; |
How to use OFFSET in the LIMIT Query.
The OFFSET value allows specifying which row to start from retrieving data.
Note: The OFFSET for the first row is 0 and not 1.
let’s suppose that if you want to retrieve data starting from the middle of the rows, then you can use the LIMIT keyword together with the OFFSET value to achieve that.
Table tblstudents
| id | Name | Roll_no | Class |
| 1 | Anuj kumar | 10806121 | B.tech |
| 2 | Sanjeev | 10883327 | MBA |
| 3 | Amit | 13123121 | BA |
| 4 | Rahul | 23424323 | LLB |
Ex:
|
1 |
Select * from tblstudents limit 2; |
The query above will limit the result set to 2rows.
Output
| id | Name | Roll_no | Class |
| 1 | Anuj kumar | 10806121 | B.tech |
| 2 | Sanjeev | 10883327 | MBA |
Ex:
|
1 |
select * from tblstudents limit 2,2; |
The query above has OFFSET=2, Hence 3rd row is return and LIMIT=2, Hence only 2 record will return
Output
| id | Name | Roll_no | Class |
| 3 | Amit | 13123121 | BA |
| 4 | Rahul | 23424323 | LLB |
