Database Reference
In-Depth Information
You can obtain the same information by running these statements without LIMIT and
ignoring everything but the first row. The advantage of LIMIT is that the server returns
only the first row, and the extra rows don't cross the network at all. This is much more
efficient than retrieving an entire result set, only to discard all but one row.
To pull rows from the middle of a result set, use the two-argument form of LIMIT , which
enables you to pick an arbitrary section of rows. 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 one, 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 |
+----+---------+------------+-------+---------------+------+
| 4 | Lothair | 1963-07-04 | blue | burrito,curry | 5 |
+----+---------+------------+-------+---------------+------+
mysql> SELECT * FROM profile ORDER BY birth DESC LIMIT 2,1;
+----+-------+------------+-------+-----------------------+------+
| id | name | birth | color | foods | cats |
+----+-------+------------+-------+-----------------------+------+
| 2 | Nancy | 1969-09-30 | white | burrito,curry,eggroll | 3 |
+----+-------+------------+-------+-----------------------+------+
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 a SE
LECT statement repeatedly, but vary its LIMIT clause like so:
SELECT ... FROM ... ORDER BY ... LIMIT 0 , 20 ;
SELECT ... FROM ... ORDER BY ... LIMIT 20 , 20 ;
SELECT ... FROM ... ORDER BY ... LIMIT 40 , 20 ;
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. Recipe 20.10 discusses
this technique further.
To determine the number of rows in a result set so that you can determine the number
of sections, issue a COUNT() statement first. For example, to display profile table rows
in name order, three at a time, you can find out how many there are with the following
statement:
mysql> SELECT COUNT(*) FROM profile;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
Search WWH ::




Custom Search