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.
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.
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
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 | +----+------+------------+-------+----------------------+------+
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
Generating Previous-Page and Next-Page Links.
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.
18.191.44.94