MySQL Cookbook

3.17 Selecting Records from the Beginning or End of a Result Set

3.17.1 Problem

You want to see only certain rows from a result set, like the first one or the last five.

3.17.2 Solution

Use a LIMIT clause, perhaps in conjunction with an ORDER BY clause.

3.17.3 Discussion

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 allows 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 allows display of smaller pages that are easier to understand.

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 records 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 |
+----+------+------------+-------+-----------------------+------+

However, because the rows in these query results aren't sorted into any particular order, 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, 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, then applies LIMIT. To find the row with the most recent birth date, the query is similar, 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 |
+----+---------+------------+-------+-------------+------+

You can obtain the same information by running these queries without LIMIT and ignoring everything but the first row. The advantage of using LIMIT is that the server returns just the first record 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.

The sort column or columns can be whatever you like. To find the row for the person with the most cats, sort by the cats column:

mysql> SELECT * FROM profile ORDER BY cats DESC LIMIT 1;
+----+------+------------+-------+---------------+------+
| id | name | birth      | color | foods         | cats |
+----+------+------------+-------+---------------+------+
|  5 | Sean | 1963-07-04 | blue  | burrito,curry |    5 |
+----+------+------------+-------+---------------+------+

However, be aware that using LIMIT n to select the "n smallest" or "n largest" values may not yield quite the results you expect. See Recipe 3.19 for some discussion on framing LIMIT queries appropriately.

To find the earliest birthday within the calendar year, sort by the month and day of the birth values:

mysql> SELECT name, DATE_FORMAT(birth,'%m-%e') AS birthday
    -> FROM profile ORDER BY birthday LIMIT 1;
+------+----------+
| name | birthday |
+------+----------+
| Alan | 02-14    |
+------+----------+

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.

3.17.4 See Also

You can use LIMIT in combination with RAND( ) to make random selections from a set of items. See Chapter 13.

As of MySQL 3.22.7, you can use LIMIT to restrict the effect of a DELETE statement to a subset of the rows that would otherwise be deleted. As of MySQL 3.23.3, the same is true for UPDATE. This can be useful in conjunction with a WHERE clause. For example, if a table contains five instances of a record, you can select them in a DELETE statement with an appropriate WHERE clause, then remove the duplicates by adding LIMIT 4 to the end of the statement. This leaves only one copy of the record. For more information about uses of LIMIT in duplicate record removal, see Chapter 14.