Recipe 3.14. Selecting Rows from the Beginning or End of a Result Set

Problem

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

Solution

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

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 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 Section 3.15 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.

See Also

Be aware that using LIMIT n to select the " n smallest" or " n largest" values may not yield quite the results you expect. See Section 3.16 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 Section 14.4.