Considering the following users
table:
id | username — | — 1 | User1 2 | User2 3 | User3 4 | User4 5 | User5
In order to constrain the number of rows in the result set of a [SELECT
query](http://dev.mysql.com/doc/refman/5.7/en/select.html), the LIMIT
clause can be used together with one or two positive integers as arguments (zero included).
LIMIT
clause with one argumentWhen one argument is used, the result set will only be constrained to the number specified in the following manner:
SELECT * FROM users ORDER BY id ASC LIMIT 2
id | username — | — 1 | User1 2 | User2
If the argument’s value is 0
, the result set will be empty.
Also notice that the ORDER BY
clause may be important in order to specify the first rows of the result set that will be presented (when ordering by another column).
LIMIT
clause with two argumentsWhen two arguments are used in a LIMIT
clause:
0
as value and thus taking into consideration the first row of the non-constrained result set.Therefore the query:
SELECT * FROM users ORDER BY id ASC LIMIT 2, 3
Presents the following result set:
id | username — | — 3 | User3 4 | User4 5 | User5
Notice that when the offset argument is 0
, the result set will be equivalent to a one argument LIMIT
clause. This means that the following 2 queries:
SELECT * FROM users ORDER BY id ASC LIMIT 0, 2
SELECT * FROM users ORDER BY id ASC LIMIT 2
Produce the same result set: