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

LIMIT n 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 that MIN⁠(⁠ ⁠ ⁠) 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 | +----+------+------------+-------+----------------------+------+ 

The two-argument form of LIMIT also makes it possible to partition a result set into smaller sections. For example, to retrieve 20 rows at a time from a result, issue the same SELECT statement repeatedly, but vary the LIMIT clauses like so:

                retrieve first 20 rows SELECT ... FROM ... ORDER BY ... LIMIT 0, 20; skip 20 rows, retrieve next 20 SELECT ... FROM ... ORDER BY ... LIMIT 20, 20; skip 40 rows, retrieve next 20 SELECT ... FROM ... ORDER BY ... LIMIT 40, 20; etc.             

Web developers often use LIMIT this way to split a large search result into smaller, more manageable pieces so that it can be presented over several pages. We'll discuss this technique further in Section 19.10.

To determine the number of rows in a result set so that you can determine how many sections there are, you can issue a COUNT⁠(⁠ ⁠ ⁠) statement first. For example, to display profile table rows in name order, four at a time, you can find out how many there are with the following statement:

mysql> SELECT COUNT(*) FROM profile; +----------+ | COUNT(*) | +----------+ |       10 | +----------+ 

That tells you that you'll have three sets of rows (although the last one will have fewer than four rows), which you can retrieve as follows:

SELECT * FROM profile ORDER BY name LIMIT 0, 4; SELECT * FROM profile ORDER BY name LIMIT 4, 4; SELECT * FROM profile ORDER BY name LIMIT 8, 4; 

You can also fetch a part of a result set and find out in the same statement how big the result would have been without the LIMIT clause. For example, to fetch the first four rows from the profile table and then obtain the size of the full result, run these statements:

SELECT SQL_CALC_FOUND_ROWS * FROM profile ORDER BY name LIMIT 4; SELECT FOUND_ROWS(); 

The keyword SQL_CALC_FOUND_ROWS in the first statement tells MySQL to calculate the size of the entire result set even though the statement requests that only part of it be returned. The row count is available by calling FOUND_ROWS⁠(⁠ ⁠ ⁠). If that function returns a value greater than four, there are other rows yet to be retrieved.