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:
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.
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References