MySQL Cookbook, 2nd Edition

Selecting Rows 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

LIMITn tells the server to return the first n rows of a result set. LIMIT also has a two-argument form that enables 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?” These are questions thatMIN() or MAX() are not suited for, but are easy with LIMIT:

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