Selecting Data from Tables - MySQL - page 122

| 1 | Sybil | 1970-04-13 | black | lutefisk,fadge,pizza | 0 |
+----+-------+------------+-------+----------------------+------+
mysql> SELECT * FROM profile LIMIT 3;
+----+-------+------------+-------+-----------------------+------+
| id | name | birth | color | foods | cats |
+----+-------+------------+-------+-----------------------+------+
| 1 | Sybil | 1970-04-13 | black | lutefisk,fadge,pizza | 0 |
| 2 | Nancy | 1969-09-30 | white | burrito,curry,eggroll | 3 |
| 3 | Ralph | 1973-11-02 | red | eggroll,pizza | 4 |
+----+-------+------------+-------+-----------------------+------+
LIMIT n means “return at most n rows.” If you specify LIMIT 10 , and the result set has
only four rows, the server returns four rows.
The rows in the preceding query results are returned in no particular order, so they may
not be very meaningful. A more common technique uses ORDER BY to sort the result set
and LIMIT to find smallest and largest values. For example, to find the row with the
minimum (earliest) birth date, sort by the birth column, then add LIMIT 1 to retrieve
the first row:
mysql> SELECT * FROM profile ORDER BY birth LIMIT 1;
+----+--------+------------+-------+----------------+------+
| id | name | birth | color | foods | cats |
+----+--------+------------+-------+----------------+------+
| 7 | Joanna | 1952-08-20 | green | lutefisk,fadge | 0 |
+----+--------+------------+-------+----------------+------+
This works because MySQL processes the ORDER BY clause to sort the rows, then applies
LIMIT .
To obtain rows from the end of a result set, sort them in the opposite order. The statement
that finds the row with the most recent birth date is similar to the previous one, except
that the sort order is descending:
mysql> SELECT * FROM profile ORDER BY birth DESC LIMIT 1;
+----+-------+------------+-------+---------------+------+
| id | name | birth | color | foods | cats |
+----+-------+------------+-------+---------------+------+
| 3 | Ralph | 1973-11-02 | red | eggroll,pizza | 4 |
+----+-------+------------+-------+---------------+------+
To find the earliest or latest birthday within the calendar year, sort by the month and
day of the birth values:
mysql> SELECT name, DATE_FORMAT(birth,'%m-%d') AS birthday
-> FROM profile ORDER BY birthday LIMIT 1;
+-------+----------+
| name | birthday |
+-------+----------+
| Henry | 02-14 |
+-------+----------+