18 Pulling a Section from the Middle of a Result Set

Pulling a Section from the Middle of a Result Set

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

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

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

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:

SELECT ... FROM ... ORDER BY ... LIMIT 0, 20; retrieve first 20 rows
SELECT ... FROM ... ORDER BY ... LIMIT 20, 20; skip 20 rows, retrieve next 20
SELECT ... FROM ... ORDER BY ... LIMIT 40, 20; skip 40 rows, retrieve next 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 Recipe 18.11.

If you want to know how large a result set is so that you can determine how many sections there are, you can issue a COUNT( ) query first. Use a WHERE clause that is the same as for the queries you'll use to retrieve the rows. For example, if you want to display profile table records in name order four at a time, you can find out how many there are with the following query:

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

That tells you you'll have three sets of rows (although the last one will have fewer than four records), 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;

Beginning with MySQL 4.0, you can fetch a part of a result set, but also find out how big the result would have been without the LIMIT clause. For example, to fetch the first four records from the profile table and then obtain the size of the full result, run these queries:

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

The keyword SQL_CALC_FOUND_ROWS in the first query tells MySQL to calculate the size of the entire result set even though the query 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 records yet to be retrieved.