Database Reference
In-Depth Information
That tells you that there are three sets of rows (the last with fewer than three rows),
which you can retrieve as follows:
SELECT
*
FROM
profile
ORDER
BY
name
LIMIT
0
,
3
;
SELECT
*
FROM
profile
ORDER
BY
name
LIMIT
3
,
3
;
SELECT
*
FROM
profile
ORDER
BY
name
LIMIT
6
,
3
;
You can also fetch the first part of a result set and determine at the same time how big
the result would have been without the
LIMIT
clause. To fetch the first three rows from
the
profile
table, and then obtain the size of the full result, run these statements:
SELECT
SQL_CALC_FOUND_ROWS
*
FROM
profile
ORDER
BY
name
LIMIT
4
;
SELECT
FOUND_ROWS
();
The keyword
SQL_CALC_FOUND_ROWS
in the first statement tells MySQL to calculate the
size of the entire result set even though the statement requests that only part of it be
returned. The row count is available by calling
FOUND_ROWS()
. If that function returns
a value greater than three, there are other rows yet to be retrieved.
See Also
LIMIT
is useful in combination with
RAND()
to make random selections from a set of
items. See
Recipe 15.8
.
You can use
LIMIT
to restrict the effect of a
DELETE
or
UPDATE
statement to a subset of
the rows that would otherwise be deleted or updated, respectively. For more information
about using
LIMIT
for duplicate row removal, see
Recipe 16.4
.
3.10. What to Do When LIMIT Requires the “Wrong”
Sort Order
Problem
LIMIT
usually works best in conjunction with an
ORDER
BY
clause that sorts rows. But
sometimes that sort order differs from what you want for the final result.
Solution
Use
LIMIT
in a subquery to retrieve the desired rows, then use the outer query to sort
them.
Discussion
If you want the last four rows of a result set, you can obtain them easily by sorting the
set in reverse order and using
LIMIT
4
. The following statement returns the names and
birth dates for the four people in the
profile
table who were born most recently:
