Sorting Query Results
Where as the WHERE conditional places restrictions on what records are returned, the ORDER BY clause will affect how that data is returned. Much as listing the columns of a table arranges the returned order (compare Figures 5.5 and 5.7), ORDER BY structures the entire list. You use this phrase like so:
SELECT * FROM tablename ORDER BY column
SELECT invoice_amount, invoice_date FROM invoices ORDER BY invoice_date
The default order when using ORDER BY is ascending, meaning that numbers increase from small to large and dates go from oldest to most recent. You can reverse this order by specifying DESC.
SELECT expense_description, expense_date FROM expenses ORDER BY expense_date DESC
You can even order the returned values by multiple columns, as I'll demonstrate.
Note that the order in which records are stored in a table is entirely meaningless. For this reason, when you do not dictate the order of the returned data, it will be presented to you in somewhat unpredictable ways. If the order of the records in important, use an ORDER BY clause.
To sort data:
Show all of the expense categories in alphabetical order (Figure 5.19
SELECT * FROM expense_categories ORDER BY expense_category;
Figure 5.19. This ORDER BY returns the fields alphabetically by expense_category.
Since the expenses
table has two columns, there are four ways of viewing the records in it: sorted by each column descending and ascending. This query will give an alphabetical presentation of the expenses
table, based upon the expense_category
Order the invoices by both client and invoice date (Figure 5.20
SELECT client_id, invoice_date, invoice_amount FROM invoices ORDER BY client_id ASC,
Figure 5.20. A double ORDER BY will sort the list by the first clause and then re-sort within that listing by the second clause.
In this query, the effect would be that every column and row is returned, first ordered by the client_id, and then by the invoice_date within the client_ids.
Because MySQL works naturally with any number of languages, the ORDER BY will be based upon the character set and collation being used. Both topics are discussed in Chapter 4.
If the column that you chose to sort on contains NULL values, those will appear first in ascending order and last in descending order.
The sorting is case-insensitive by default. You can make a text column sort in a case-sensitive fashion using the keyword BINARY: SELECT * FROM table ORDER BY BINARY col Technically this tells MySQL not to use a collation, but that normally has the effect of a case-sensitive search.
You can, and frequently will, use ORDER BY in conjunction with WHERE, joins, or other clauses. When doing so, place the ORDER BY after the other conditions.
SELECT invoice_id, invoice_amount, invoice_date FROM invoices WHERE invoice_date <=
'2006-03-01 ORDER BY invoice_date DESC
It is up to the database application to determine how results are returned when you do not use an ORDER BY clause. With MySQL, this is currently likely to be on the primary key ascending, but that could always change. As a rule of thumb, remember that the order in which records are stored and retrieved is entirely meaningless. Use ORDER BY to create meaning.