Ordering data

This section of the online MySQL tutorial looks at how we can change the display order of the data extracted from MySQL tables using the ORDER BY clause of the SELECT statement.

The data that we have retrieved so far was always displayed in the order in which it was stored in the table. Actually, SQL allows for sorting of retrieved data with the ORDER BY clause. This clause requires the column name based on which the data will be sorted. Let’s see how to display employee names with last names sorted alphabetically (in ascending order).

SELECT l_name, f_name from
employee_data ORDER BY l_name;

+------------+---------+
| l_name     | f_name  |
+------------+---------+
| Ali        | Shahida |
| Anchor     | Mary    |
| Champion   | Peter   |
| Gibson     | Danny   |
| Hagan      | John    |
| Harper     | Mike    |
| Hoopla     | Arthur  |
| Hunter     | Kim     |
| Irvine     | Joseph  |
| Kruger     | Fred    |
| Lewis      | Roger   |
| MacFarland | John    |
| Nanda      | Alok    |
| Pandit     | Anamika |
| Pillai     | Ganesh  |
| Rajabi     | Hassan  |
| Sakamuro   | Edward  |
| Sehgal     | Monica  |
| Sharma     | Manish  |
| Simlai     | Hal     |
| Simon      | Paul    |
+------------+---------+
21 rows in set (0.00 sec)

Here are employees sorted by age.

SELECT f_name, l_name, age
from employee_data
ORDER BY age;

+---------+------------+------+
| f_name  | l_name     | age  |
+---------+------------+------+
| Edward  | Sakamuro   |   25 |
| Mary    | Anchor     |   26 |
| Anamika | Pandit     |   27 |
| Hal     | Simlai     |   27 |
| Joseph  | Irvine     |   27 |
| Manish  | Sharma     |   28 |
| Monica  | Sehgal     |   30 |
| Fred    | Kruger     |   31 |
| John    | Hagan      |   32 |
| Ganesh  | Pillai     |   32 |
| Alok    | Nanda      |   32 |
| Arthur  | Hoopla     |   32 |
| Kim     | Hunter     |   32 |
| Shahida | Ali        |   32 |
| Hassan  | Rajabi     |   33 |
| John    | MacFarland |   34 |
| Danny   | Gibson     |   34 |
| Roger   | Lewis      |   35 |
| Mike    | Harper     |   36 |
| Peter   | Champion   |   36 |
| Paul    | Simon      |   43 |
+---------+------------+------+
21 rows in set (0.00 sec)

The ORDER BY clause can sort in an ASCENDING (ASC) or DESCENDING (DESC) order depending upon the argument supplied.

To list employee first names in descending order, we’ll use the statement below.

SELECT f_name from employee_data
ORDER by f_name DESC;

+---------+
| f_name  |
+---------+
| Shahida |
| Roger   |
| Peter   |
| Paul    |
| Monica  |
| Mike    |
| Mary    |
| Manish  |
| Kim     |
| Joseph  |
| John    |
| John    |
| Hassan  |
| Hal     |
| Ganesh  |
| Fred    |
| Edward  |
| Danny   |
| Arthur  |
| Anamika |
| Alok    |
+---------+
21 rows in set (0.00 sec)

Note: The ascending (ASC) order is the default.