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 | +----+------+------------+-------+----------------------+------+ ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.