Specifying Sort Direction
| Data sorting is not limited to ascending sort orders (from A to Z). Although this is the default sort order, the ORDER BY clause can also be used to sort in descending order (from Z to A). To sort by descending order, the keyword DESC must be specified. The following example sorts the products by price in descending order (most expensive first): • Input SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC; • Output +---------+------------+----------------+ | prod_id | prod_price | prod_name | +---------+------------+----------------+ | JP2000 | 55.00 | JetPack 2000 | | SAFE | 50.00 | Safe | | JP1000 | 35.00 | JetPack 1000 | | ANV03 | 14.99 | 2 ton anvil | | DTNTR | 13.00 | Detonator | | TNT2 | 10.00 | TNT (5 sticks) | | FB | 10.00 | Bird seed | | ANV02 | 9.99 | 1 ton anvil | | OL1 | 8.99 | Oil can | | ANV01 | 5.99 | .5 ton anvil | | SLING | 4.49 | Sling | | FU1 | 3.42 | Fuses | | FC | 2.50 | Carrots | | TNT1 | 2.50 | TNT (1 stick) | +---------+------------+----------------+ But what if you were to sort by multiple columns? The following example sorts the products in descending order (most expensive first), plus product name: • Input SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name; • Output +---------+------------+----------------+ | prod_id | prod_price | prod_name | +---------+------------+----------------+ | JP2000 | 55.00 | JetPack 2000 | | SAFE | 50.00 | Safe | | JP1000 | 35.00 | JetPack 1000 | | ANV03 | 14.99 | 2 ton anvil | | DTNTR | 13.00 | Detonator | | FB | 10.00 | Bird seed | | TNT2 | 10.00 | TNT (5 sticks) | | ANV02 | 9.99 | 1 ton anvil | | OL1 | 8.99 | Oil can | | ANV01 | 5.99 | .5 ton anvil | | SLING | 4.49 | Sling | | FU1 | 3.42 | Fuses | | FC | 2.50 | Carrots | | TNT1 | 2.50 | TNT (1 stick) | +---------+------------+----------------+ • Analysis The DESC keyword only applies to the column name that directly precedes it. In the previous example, DESC was specified for the prod_price column, but not for the prod_name column. Therefore, the prod_price column is sorted in descending order, but the prod_name column (within each price) is still sorted in standard ascending order. Tip Sorting Descending on Multiple Columns If you want to sort descending on multiple columns, be sure each column has its own DESC keyword. The opposite of DESC is ASC (for ascending), which may be specified to sort in ascending order. In practice, however, ASC is not usually used because ascending order is the default sequence (and is assumed if neither ASC nor DESC are specified). Tip Case Sensitivity and Sort Orders When you are sorting textual data, is A the same as a? And does a come before B or after Z? These are not theoretical questions, and the answers depend on how the database is set up. In dictionary sort order, A is treated the same as a, and that is the default behavior in MySQL (and indeed most DBMSs). However, administrators can change this behavior if needed. (If your database contains lots of foreign language characters, this might become necessary.) The key here is that, if you do need an alternate sort order, you cannot accomplish it with a simple ORDER BY clause. You must contact your database administrator. Using a combination of ORDER BY and LIMIT, it is possible to find the highest or lowest value in a column. The following example demonstrates how to find the value of the most expensive item: • Input SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1; • Output +------------+ | prod_price | +------------+ | 55.00 | +------------+ • Analysis prod_price DESC ensures that rows are retrieved from most to least expensive, and LIMIT 1 tells MySQL to just return one row. Caution Position of ORDER BY Clause When specifying an ORDER BY clause, be sure that it is after the FROM clause. If LIMIT is used, it must come after ORDER BY. Using clauses out of order will generate an error message. |
