Database Reference
In-Depth Information
mysql>
SELECT * FROM profile_contact
->
WHERE profile_id = (SELECT id FROM profile WHERE name = 'Nancy');
+------------+----------+--------------+
| profile_id | service | contact_name |
+------------+----------+--------------+
| 2 | Twitter | user2-fbrid |
| 2 | Facebook | user2-msnid |
| 2 | LinkedIn | user2-lnkdid |
+------------+----------+--------------+
Here the subquery appears as a nested
SELECT
statement enclosed within parentheses.
3.9. Selecting Rows from the Beginning, End, or Middle of
Query Results
Problem
You want only certain rows from a result set, such as the first one, the last five, or rows
21 through 40.
Solution
Use a
LIMIT
clause, perhaps in conjunction with an
ORDER
BY
clause.
Discussion
MySQL supports a
LIMIT
clause that tells the server to return only part of a result set.
LIMIT
is a MySQL-specific extension to SQL that is extremely valuable when your result
set contains more rows than you want to see at a time. It enables you to retrieve an
arbitrary section of a result set. Typical
LIMIT
uses include the following kinds of prob‐
lems:
• Answering questions about first or last, largest or smallest, newest or oldest, least
or most expensive, and so forth.
• Splitting a result set into sections so that you can process it one piece at a time. This
technique is common in web applications for displaying a large search result across
several pages. Showing the result in sections enables display of smaller, easier-to-
understand pages.
The following examples use the
profile
table shown in
Recipe 3.8
. To see the first
n
rows of a
SELECT
result, add
LIMIT
n
to the end of the statement:
mysql>
SELECT * FROM profile LIMIT 1;
+----+-------+------------+-------+----------------------+------+
| id | name | birth | color | foods | cats |
+----+-------+------------+-------+----------------------+------+