This section of the MySQL primer 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.
Assignment:
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.
Possible Answers:
1.mysql> SELECT f_name, l_name, salary
-> from employee_data
-> ORDER BY salary;
+———+————+——–+
| f_name | l_name | salary |
+———+————+——–+
| Alok | Nanda | 70000 |
| Hal | Simlai | 70000 |
| Shahida | Ali | 70000 |
| Joseph | Irvine | 72000 |
| Fred | Kruger | 75000 |
| Edward | Sakamuro | 75000 |
| Arthur | Hoopla | 75000 |
| John | MacFarland | 80000 |
| Mary | Anchor | 85000 |
| Paul | Simon | 85000 |
| Anamika | Pandit | 90000 |
| Hassan | Rajabi | 90000 |
| Danny | Gibson | 90000 |
| Monica | Sehgal | 90000 |
| Roger | Lewis | 100000 |
| Ganesh | Pillai | 110000 |
| Kim | Hunter | 110000 |
| John | Hagan | 120000 |
| Mike | Harper | 120000 |
| Peter | Champion | 120000 |
| Manish | Sharma | 200000 |
+———+————+——–+
21 rows in set (0.00 sec)
2.mysql> SELECT f_name, l_name, yos
-> from employee_data
-> ORDER by yos;
+———+————+——+
| f_name | l_name | yos |
+———+————+——+
| Arthur | Hoopla | 1 |
| Danny | Gibson | 1 |
| Mary | Anchor | 2 |
| Edward | Sakamuro | 2 |
| Paul | Simon | 2 |
| Kim | Hunter | 2 |
| Roger | Lewis | 2 |
| Mike | Harper | 2 |
| Hal | Simlai | 2 |
| Joseph | Irvine | 2 |
| Anamika | Pandit | 3 |
| Fred | Kruger | 3 |
| Alok | Nanda | 3 |
| Hassan | Rajabi | 3 |
| Monica | Sehgal | 3 |
| Shahida | Ali | 3 |
| Manish | Sharma | 4 |
| John | Hagan | 4 |
| Ganesh | Pillai | 4 |
| John | MacFarland | 4 |
| Peter | Champion | 4 |
+———+————+——+
21 rows in set (0.01 sec)
3.The statement displays the ids, last names, titles and ages of all employees sorted on titles in descending order and age in ascending order.
mysql> SELECT emp_id, l_name, title, age
-> from employee_data ORDER BY
-> title DESC, age ASC;
+——–+————+—————————-+——+
| emp_id | l_name | title | age |
+——–+————+—————————-+——+
| 5 | Anchor | Web Designer | 26 |
| 4 | Pandit | Web Designer | 27 |
| 15 | Gibson | System Administrator | 34 |
| 14 | Lewis | System Administrator | 35 |
| 13 | Hunter | Senior Web Designer | 32 |
| 2 | Hagan | Senior Programmer | 32 |
| 3 | Pillai | Senior Programmer | 32 |
| 16 | Harper | Senior Marketing Executive | 36 |
| 8 | Sakamuro | Programmer | 25 |
| 6 | Kruger | Programmer | 31 |
| 9 | Nanda | Programmer | 32 |
| 7 | MacFarland | Programmer | 34 |
| 12 | Hoopla | Multimedia Programmer | 32 |
| 10 | Rajabi | Multimedia Programmer | 33 |
| 11 | Simon | Multimedia Programmer | 43 |
| 18 | Simlai | Marketing Executive | 27 |
| 19 | Irvine | Marketing Executive | 27 |
| 17 | Sehgal | Marketing Executive | 30 |
| 21 | Champion | Finance Manager | 36 |
| 20 | Ali | Customer Service Manager | 32 |
| 1 | Sharma | CEO | 28 |
+——–+————+—————————-+——+
21 rows in set (0.00 sec)
Note: The titles are first sorted in descending order. Then in each title, the ages are further sorted in ascending order.
4.mysql> SELECT l_name, f_name from employee_data
-> where title IN ('Programmer',
-> 'Web Designer') ORDER BY l_name;
+————+———+
| l_name | f_name |
+————+———+
| Anchor | Mary |
| Kruger | Fred |
| MacFarland | John |
| Nanda | Alok |
| Pandit | Anamika |
| Sakamuro | Edward |
+————+———+
6 rows in set (0.00 sec)