# Limit and Offset
# Limit and Offset relationship
Considering the following
In order to constrain the number of rows in the result set of a
SELECT query, the
LIMIT clause can be used together with one or two positive integers as arguments (zero included).
LIMIT clause with one argument
When 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
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).
LIMITclause with two arguments
When two arguments are used in a
- the first argument represents the row from which the result set rows will be presented – this number is often mentioned as an offset, since it represents the row previous to the initial row of the constrained result set. This allows the argument to receive
0as value and thus taking into consideration the first row of the non-constrained result set.
- the second argument specifies the maximum number of rows to be returned in the result set (similarly to the one argument's example).
Therefore the query:
SELECT * FROM users ORDER BY id ASC LIMIT 2, 3
Presents the following result set:
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:
OFFSET keyword: alternative syntax
An alternative syntax for the
LIMIT clause with two arguments consists in the usage of the
OFFSET keyword after the first argument in the following manner:
SELECT * FROM users ORDER BY id ASC LIMIT 2 OFFSET 3
This query would return the following result set:
Notice that in this alternative syntax the arguments have their positions switched:
ORDER BY order_column
LIMIT row_count [OFFSET row_offset]
ORDER BY order_column
LIMIT [row_offset,] row_count
"Limit" could mean "Max number of rows in a table".
"Offset" mean pick from
row number (not to be confused by primary key value or any field data value)