MySQL supports a LIMIT
clause
that tells the server to return only part of a result set. LIMIT
is a MySQL-specific extension to SQL
that is extremely valuable when your result set contains more rows
than you want to see at a time. It enables you to retrieve just the
first part of a result set or an arbitrary section of the set.
Typically, LIMIT
is used for the
following kinds of problems:
Answering questions about first or last, largest or smallest, newest or oldest, least or more expensive, and so forth.
Splitting a result set into sections so that you can process it one piece at a time. This technique is common in web applications for displaying a large search result across several pages. Showing the result in sections enables display of smaller pages that are easier to understand. See Selecting Rows from the Middle of a Result Set for details on this.
The following examples use the profile
table that was introduced in Chapter 2. Its contents look like this:
mysql>SELECT * FROM profile;
+----+---------+------------+-------+-----------------------+------+
| id | name | birth | color | foods | cats |
+----+---------+------------+-------+-----------------------+------+
| 1 | Fred | 1970-04-13 | black | lutefisk,fadge,pizza | 0 |
| 2 | Mort | 1969-09-30 | white | burrito,curry,eggroll | 3 |
| 3 | Brit | 1957-12-01 | red | burrito,curry,pizza | 1 |
| 4 | Carl | 1973-11-02 | red | eggroll,pizza | 4 |
| 5 | Sean | 1963-07-04 | blue | burrito,curry | 5 |
| 6 | Alan | 1965-02-14 | red | curry,fadge | 1 |
| 7 | Mara | 1968-09-17 | green | lutefisk,fadge | 1 |
| 8 | Shepard | 1975-09-02 | black | curry,pizza | 2 |
| 9 | Dick | 1952-08-20 | green | lutefisk,fadge | 0 |
| 10 | Tony | 1960-05-01 | white | burrito,pizza | 0 |
+----+---------+------------+-------+-----------------------+------+
To select the first n
rows of a query
result, add LIMIT
n
to the end of your SELECT
statement:
mysql>SELECT * FROM profile LIMIT 1;
+----+------+------------+-------+----------------------+------+ | id | name | birth | color | foods | cats | +----+------+------------+-------+----------------------+------+ | 1 | Fred | 1970-04-13 | black | lutefisk,fadge,pizza | 0 | +----+------+------------+-------+----------------------+------+ mysql>SELECT * FROM profile LIMIT 5;
+----+------+------------+-------+-----------------------+------+ | id | name | birth | color | foods | cats | +----+------+------------+-------+-----------------------+------+ | 1 | Fred | 1970-04-13 | black | lutefisk,fadge,pizza | 0 | | 2 | Mort | 1969-09-30 | white | burrito,curry,eggroll | 3 | | 3 | Brit | 1957-12-01 | red | burrito,curry,pizza | 1 | | 4 | Carl | 1973-11-02 | red | eggroll,pizza | 4 | | 5 | Sean | 1963-07-04 | blue | burrito,curry | 5 | +----+------+------------+-------+-----------------------+------+
Note that LIMIT
n
really means “return at most
n
rows.” If you specify LIMIT
10
,
and the result set has only 3 rows, the server returns 3 rows.
The rows in the preceding query results aren’t sorted into any
particular order, so they may not be very meaningful. A more common
technique is to use ORDER
BY
to sort the result set. Then you can use
LIMIT
to find smallest and largest
values. For example, to find the row with the minimum (earliest) birth
date, sort by the birth
column, and
then add LIMIT
1
to retrieve the first row:
mysql>SELECT * FROM profile ORDER BY birth LIMIT 1;
+----+------+------------+-------+----------------+------+
| id | name | birth | color | foods | cats |
+----+------+------------+-------+----------------+------+
| 9 | Dick | 1952-08-20 | green | lutefisk,fadge | 0 |
+----+------+------------+-------+----------------+------+
This works because MySQL processes the ORDER
BY
clause to sort the rows first, and then applies LIMIT
.
To obtain rows from the end of a result set, sort them in the opposite order. The statement that finds the row with the most recent birth date is similar to the previous one, except that you sort in descending order:
mysql>SELECT * FROM profile ORDER BY birth DESC LIMIT 1;
+----+---------+------------+-------+-------------+------+
| id | name | birth | color | foods | cats |
+----+---------+------------+-------+-------------+------+
| 8 | Shepard | 1975-09-02 | black | curry,pizza | 2 |
+----+---------+------------+-------+-------------+------+
To find the earliest or latest birthday within the calendar
year, sort by the month and day of the birth
values:
mysql>SELECT name, DATE_FORMAT(birth,'%m-%d') AS birthday
->FROM profile ORDER BY birthday LIMIT 1;
+------+----------+ | name | birthday | +------+----------+ | Alan | 02-14 | +------+----------+
You can obtain the same information by running these statements
without LIMIT
and ignoring
everything but the first row. The advantage of using LIMIT
is that the server returns just the
first row, and the extra rows don’t travel over the network at all.
This is much more efficient than retrieving an entire result set, only
to discard all but one row.
Be aware that using LIMIT
n
to select the
“n
smallest” or
“n
largest” values may not
yield quite the results you expect. See Choosing Appropriate LIMIT Values for some discussion on
framing LIMIT
clauses appropriately
for the questions that you are asking.
LIMIT
is useful in
combination with RAND()
to
make random selections from a set of items. See Chapter 13.
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. This can be
useful in conjunction with a WHERE
clause. For example, if a table contains five instances of a row, you
can select them in a DELETE
statement with an appropriate WHERE
clause, and then remove the duplicates by adding LIMIT
4
to the end of the statement. This leaves only one copy of the row. For
more information about uses of LIMIT
in duplicate row removal,
see Eliminating Duplicates from a Table.
3.20.224.107