Database Reference
In-Depth Information
You can obtain the same information by running these statements without
LIMIT
and
ignoring everything but the first row. The advantage of
LIMIT
is that the server returns
only the first row, and the extra rows don't cross the network at all. This is much more
efficient than retrieving an entire result set, only to discard all but one row.
To pull rows from the middle of a result set, use the two-argument form of
LIMIT
, which
enables you to pick an arbitrary section of rows. The arguments indicate how many
rows to skip and how many to return. This means that you can use
LIMIT
to do such
things as skip two rows and return the next one, thus answering questions such as “What
is the
third-
smallest or
third-
largest value?” These are questions that
MIN()
or
MAX()
are
not suited for, but are easy with
LIMIT
:
mysql>
SELECT * FROM profile ORDER BY birth LIMIT 2,1;
+----+---------+------------+-------+---------------+------+
| id | name | birth | color | foods | cats |
+----+---------+------------+-------+---------------+------+
| 4 | Lothair | 1963-07-04 | blue | burrito,curry | 5 |
+----+---------+------------+-------+---------------+------+
mysql>
SELECT * FROM profile ORDER BY birth DESC LIMIT 2,1;
+----+-------+------------+-------+-----------------------+------+
| id | name | birth | color | foods | cats |
+----+-------+------------+-------+-----------------------+------+
| 2 | Nancy | 1969-09-30 | white | burrito,curry,eggroll | 3 |
+----+-------+------------+-------+-----------------------+------+
The two-argument form of
LIMIT
also makes it possible to partition a result set into
smaller sections. For example, to retrieve 20 rows at a time from a result, issue a
SE
LECT
statement repeatedly, but vary its
LIMIT
clause like so:
SELECT
...
FROM
...
ORDER
BY
...
LIMIT
0
,
20
;
SELECT
...
FROM
...
ORDER
BY
...
LIMIT
20
,
20
;
SELECT
...
FROM
...
ORDER
BY
...
LIMIT
40
,
20
;
…
Web developers often use
LIMIT
this way to split a large search result into smaller, more
manageable pieces so that it can be presented over several pages.
Recipe 20.10
discusses
this technique further.
To determine the number of rows in a result set so that you can determine the number
of sections, issue a
COUNT()
statement first. For example, to display
profile
table rows
in name order, three at a time, you can find out how many there are with the following
statement:
mysql>
SELECT COUNT(*) FROM profile;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+