Clauses, Sorting, and SQL Queries - Ascending Versus Descending Sort Order
(Page 3 of 5 )
When sorting, you have the option of specifying ascending or descending order via the asc and desc keywords. The default is ascending, so you will only need to add thedesckeyword if you want to use a descending sort. For example, the following query lists all accounts sorted by available balance with the highest balance listed at the top:
mysql> SELECT account_id, product_cd, open_date, avail_balance
-> FROM account
-> ORDER BY avail_balance DESC;
|
account_id product_cd open_date | avail_balance |
|
| 24 SBL | 2004-02-22 | 50000.00 |
| 23 CHK | 2003-07-30 | 38552.05 |
| 20 CHK | 2002-09-30 | 23575.12 |
| 13 CD | 2004-12-28 | 10000.00 |
| 22 BUS | 2004-03-22 | 9345.55 |
| 18 MM | 2004-10-28 | 9345.55 |
| 10 MM | 2004-09-30 | 5487.09 |
| 14 CD | 2004-01-12 | 5000.00 |
| 15 CHK | 2001-05-23 | 3487.19 |
| 3 CD | 2004-06-30 | 3000.00 |
| 4 CHK | 2001-03-12 | 2258.02 |
| 11 CHK | 2004-01-27 | 2237.97 |
| 7 MM | 2002-12-15 | 2212.50 |
| 19 CD | 2004-06-30 | 1500.00 |
| 1 CHK | 2000-01-15 | 1057.75 |
| 6 CHK | 2002-11-23 | 1057.75 |
| 9 SAV | 2000-01-15 | 767.77 |
| 8 CHK | 2003-09-12 | 534.12 |
| 2 SAV | 2000-01-15 | 500.00 |
| 16 SAV | 2001-05-23 | 387.99 |
| 5 SAV | 2001-03-12 | 200.00 |
| 17 CHK | 2003-07-30 | 125.67 |
| 12 CHK | 2002-08-24 | 122.37 |
| 21 BUS | 2002-10-01 | 0.00 |
|
24 rows in set (0.01 sec)
Descending sorts are commonly used for ranking queries, such as “show me the top 5 account balances.” MySQL includes alimitclause that allows you to sort your data and then discard all but the first X rows; see Appendix B for a discussion of thelimitclause, along with other non-ANSI extensions.
Next: Sorting via Expressions >>
More Database Articles Articles
More By O'Reilly Media
|
 This article is excerpted from chapter three of the book Learning SQL, written by Alan Beaulieu (O'Reilly; ISBN: 0596007272). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 4 
