MySQL

Sort Data using ORDER BY

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.

SELECT prod_name
FROM products;

The above statement displays the following output:

+------------+
| prod_name  |
+------------+
| soaps      |
| chocolates |
| juices     |
| flour      |
| oil        |
+------------+

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 MySQL 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.

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:

SELECT prod_name
FROM products
ORDER BY prod_name;

This statement is identical to the earlier statement, except it also specifies an ORDER BY clause instructing MySQL to sort the data alphabetically by the prod_name column.

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.

The results are as follows:

+------------+
| prod_name  |
+------------+
| chocolates |
| flour      |
| juices     |
| oil        |
| soaps      |
+------------+