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 |
+----+-------+------------+-------+----------------------+------+
Search WWH ::




Custom Search