MariaDB Crash Course - Ben Forta [16]
Summary
In this chapter, you learned how to use the SQL SELECT statement to retrieve a single table column, multiple table columns, and all table columns. You also learned about commenting and saw various ways that comments can be used. Next you learn how to sort the retrieved data.
5. Sorting Retrieved Data
In this chapter, you learn how to use the SELECT statement’s ORDER BY clause to sort retrieved data as needed.
Sorting Data
As you learned in Chapter 4, “Retrieving Data,” the following SQL statement returns a single column from a database table. But look at the output. The data appears to be displayed in no particular order at all.
Input
SELECT prod_name
FROM products;
Output
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Oil can |
| Fuses |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
| Bird seed |
| Carrots |
| Safe |
| Detonator |
| JetPack 1000 |
| JetPack 2000 |
+----------------+
Actually, the retrieved data is not displayed in a mere random order. If unsorted, data is typically displayed in the order in which it appears in the underlying tables. This could be the order in which the data was added to the tables initially. However, if data was subsequently updated or deleted, the order is affected by how MariaDB reuses reclaimed storage space. The end result is that you cannot (and should not) rely on the sort order if you do not explicitly control it. Relational database design theory states that the sequence of retrieved data cannot be assumed to have significance if ordering was not explicitly specified.
* * *
New Term: Clause
SQL statements are made up of clauses, some required and some optional. A clause usually consists of a keyword and supplied data. An example of this is the SELECT statement’s FROM clause, which you saw in the last chapter.
* * *
To explicitly sort data retrieved using a SELECT statement, the ORDER BY clause is used. ORDER BY takes the name of one or more columns by which to sort the output. Look at the following example:
Input
SELECT prod_name
FROM products
ORDER BY prod_name;
Analysis
This statement is identical to the earlier statement, except it also specifies an ORDER BY clause instructing MariaDB to sort the data alphabetically by the prod_name column. The results are as follows:
Output
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Bird seed |
| Carrots |
| Detonator |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
| Safe |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
* * *
Tip: Sorting by Nonselected Columns
More often than not, the columns used in an ORDER BY clause are ones that were selected for display. However, this is actually not required, and it is perfectly legal to sort data by a column that is not retrieved.
* * *
Sorting by Multiple Columns
It is often necessary to sort data by more than one column. For example, if you are displaying an employee list, you might want to display it sorted by last name and first name (first sort by last name, and then within each last name sort by first name). This would be useful if there are multiple employees with the same last name.
To sort by multiple columns, simply specify the column names separated by commas (just as you do when you are selecting multiple columns).
The following code retrieves three columns and sorts the results by two of them—first by price and then by name.
Input
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
Output
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| FC | 2.50 | Carrots |
| TNT1 | 2.50 | TNT (1 stick) |
| FU1 | 3.42 | Fuses |
| SLING | 4.49 | Sling |
| ANV01 | 5.99 | .5 ton anvil |
| OL1 | 8.99 | Oil can |
| ANV02 | 9.99 | 1 ton anvil |
| FB | 10.00 | Bird seed |
| TNT2 | 10.00