MariaDB Crash Course - Ben Forta [17]
| DTNTR | 13.00 | Detonator |
| ANV03 | 14.99 | 2 ton anvil |
| JP1000 | 35.00 | JetPack 1000 |
| SAFE | 50.00 | Safe |
| JP2000 | 55.00 | JetPack 2000 |
+---------+------------+----------------+
It is important to understand that when you are sorting by multiple columns, the sort sequence is exactly as specified. In other words, using the output in the previous example, the products are sorted by the prod_name column only when multiple rows have the same prod_price value. If all the values in the prod_price column had been unique, no data would have been sorted by prod_name.
* * *
Tip: An ORDER BY Shortcut
Instead of type the column names in ORDER BY, you can also type the column number specifying its sequence in the SELECT statement. This statement:
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
is functionally identical to this statement:
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY 2, 3;
Obviously, this syntax can save you some typing. But, keep in mind that if you do use this shortcut, then your ORDER BY statement will essentially break if you ever make changes to the SELECT columns.
* * *
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 applies only 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