Database Reference
In-Depth Information
Ordering Results
The previous example selectedspecific columns from the birds table and limited the res-
ults with the LIMIT clause. However, the rows were listed in whatever order they were
found in the table. We've decided to see only a tiny subset of the birds in the Charadriidae
family, so ordering can make a difference. If we want to put the results in alphabetical or-
der based on the values of the common_name column, we add an ORDER BY clause like
this:
SELECT common_name, scientific_name
FROM birds WHERE family_id = 103
ORDER BY common_name
LIMIT 3;
+-----------------------+----------------------+
| common_name | scientific_name |
+-----------------------+----------------------+
| Black-bellied Plover | Pluvialis squatarola |
| Mountain Plover | Charadrius montanus |
| Pacific Golden Plover | Pluvialis fulva |
+-----------------------+----------------------+
Notice that the ORDER BY clause is located afterthe WHERE clause and before the LIMIT
clause. Not only will this statement display the rows in order by common_name , but it
will retrieve only the first three rows based on the ordering. That is to say, MySQL will
first retrieve all of the rows based on the WHERE clause, store those results in a temporary
table behind the scenes, order the data based on the ORDER BY clause, and then return to
the mysql client the first three rows found in that temporary table based on the LIMIT
clause. This activity is the reason for the positioning of each clause.
By default, the ORDER BY clause uses ascending order, which means from A to Z for an
alphabetic column. If you want to display data in descending order, addthe DESC option,
as in ORDER BY DESC . There's also a contrasting ASC option, but you probably won't
need to use it because ascending order is the default.
To order by more than one column, give all the columns in the ORDER BY clause in a
comma-separated list. Each column can be sorted in ascending or descending order. The
clause sorts all the data by the first column you specify, and then within that order by the
second column, etc. To illustrate this, we'll select another column from the birds table,
family_id , and we'll get birds from a few more families. We'll select some other types
of shore birds: Oystercatchers (i.e., Haematopodidae ), Stilts (e.g., Recurvirostridae ), and
Search WWH ::




Custom Search