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

1. Order all employees on the basis of the salary they draw.
2. List all employees in descending order of their years of service.
3. What does the following statement display?

   SELECT emp_id, l_name, title, age
   from employee_data ORDER BY
   title DESC, age ASC;
   

4. Display employees (last names followed by first names) who hold the title of either "Programmer" or "Web Designer" and sort their last names alphabetically.