MySQL Cookbook, 2nd Edition

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