Clauses, Sorting, and SQL Queries - Ascending Versus Descending Sort Order

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