MySQL Limit Clause Statement

Tutorials

Mysql

Topic

How to use MySQL Limit Clause In Select Statement?

Explanation

MySQL Limit Clause Statement:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. It takes one or two numeric arguments, which must both be non-negative integer constants.

Lets see an example query for SELECT LIMIT statement.


mysql> select * from student limit 2,5;
+--------+---------+-------+-----------------+---------+
| studid | name    | marks | address         | phone   |
+--------+---------+-------+-----------------+---------+
|      3 | michael |    75 | edinburgh       | 2598234 |
|      4 | jack    |    82 | victoria street | 2436821 |
|      5 | anne    |   100 | downing street  | 2634821 |
|      6 | steve   |    75 | downing street  | 2874698 |
|      7 | anne    |    80 | edinburgh       | 2569843 |
+--------+---------+-------+-----------------+---------+
5 rows in set (0.00 sec)

Here the first argument of th query specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. Therefore it retrieves the rows from 3 - 7 from the student table.

We can also return the values from the beginning of the result set by specifying the number of rows in a single argument as follows.


mysql> select * from student limit 5;
+--------+---------+-------+------------------+---------+
| studid | name    | marks | address          | phone   |
+--------+---------+-------+------------------+---------+
|      1 | steve   |   100 | 5th cross street | 2456987 |
|      2 | david   |    98 | welling street   |  547896 |
|      3 | michael |    75 | edinburgh        | 2598234 |
|      4 | jack    |    82 | victoria street  | 2436821 |
|      5 | anne    |   100 | downing street   | 2634821 |
+--------+---------+-------+------------------+---------+
5 rows in set (0.08 sec)

The above query will retrieve the first five rows of the student table.