MySQL Cookbook

Selecting Records from the Beginning or End of a Result Set

Problem

You want to see only certain rows from a result set, like 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 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 ...