MySQL Cookbook

Pulling a Section from the Middle of a Result Set

Problem

You don’t want the first or last rows of a result set. Instead, you want to pull a section of rows out of the middle of the set, such as rows 21 through 40.

Solution

That’s still a job for LIMIT. But you need to tell it the starting position within the result set in addition to the number of rows you want.

Discussion

LIMIT n tells the server to return the first n rows of a result set. LIMIT also has a two-argument form that allows you to pick out any arbitrary section of rows from a result. The arguments indicate how many rows to skip and how many to return. This means that you can use LIMIT to do such things as skip two rows and return the next, thus answering questions such as “what is the third-smallest or third-largest value?,” something that’s more difficult with MIN( ) or MAX( ):

mysql> SELECT * FROM profile ORDER BY birth LIMIT 2,1;
+----+------+------------+-------+---------------+------+
| id | name | birth      | color | foods         | cats |
+----+------+------------+-------+---------------+------+
| 10 | Tony | 1960-05-01 | white | burrito,pizza |    0 |
+----+------+------------+-------+---------------+------+
mysql> SELECT * FROM profile ORDER BY birth DESC LIMIT 2,1; +----+------+------------+-------+----------------------+------+ | id | name | birth | color | foods | cats | +----+------+------------+-------+----------------------+------+ | 1 | Fred | 1970-04-13 | black | lutefisk,fadge,pizza | 0 | +----+------+------------+-------+----------------------+------+ ...