Sorting Data Retrieved from a MySQL Database

Now that we have looked at the basics of retrieving data from a MySQL database and before we look and filtering retrieved data we need to look at how to sort the output from a database retrieval.

Sorting data retrieved using SELECT

In the previous chapter we look at the SQL SELECT statement and how it is used to retrieve data from a database. Typically, a SELECT statement will extract the data from a table in the order in which it was added. Thius, however, can change if the database table has been subjected to updates and deletions. Quite simply, there is no way to predict the exact order in which data will be retrieved. For this reason SQL provides the ORDER BY keywords which enable us to sort the data as it is retrieved by the SELECT statement.

Let's begin by performing a retrieval on a sample database:

USE MySampleDB;
SELECT prod_desc FROM product;

When this command is executed, the data is extracted it appears on the surface as thoug the rows have been retrieved in no particular order:

+-----------------------------+
| prod_desc                   |
+-----------------------------+
| CD Writer                   |
| Cordless Mouse              |
| SATA Disk Drive             |
| Ergonmoc Keyboard           |
| Smart Phone                 |
| Mobile Phone                |
| Desktop PC                  |
| Portable Music/Movie Player |
| Optical Mouse               |
| Wireless Ethernet Bridge    |
+-----------------------------+

However, if instead of retrieving just the prod_desc column we retrieve all the columns stored in the table we see that the results were listed in the order of the prod_code column. Hardly surpirsing since this is an auto-increment value and reflects the order in which the data was added (although as mentioned previously there is no certainty that results will be in this order after a table has been subjected to deletions and updates):

+-----------+----------------------------+-----------------------------+
| prod_code | prod_name                  | prod_desc                   |
+-----------+----------------------------+-----------------------------+
|         1 | CD-RW Model 4543           | CD Writer                   |
|         2 | EasyTech Mouse 7632        | Cordless Mouse              |
|         3 | WildTech 250Gb 1700        | SATA Disk Drive             |
|         4 | Microsoft 10-20 Keyboard   | Ergonmoc Keyboard           |
|         5 | Apple iPhone 8Gb           | Smart Phone                 |
|         6 | Moto Razr                  | Mobile Phone                |
|         7 | Dell XPS 400               | Desktop PC                  |
|         8 | Apple iPod Touch           | Portable Music/Movie Player |
|         9 | Kensington Ci20            | Optical Mouse               |
|        10 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge    |
+-----------+----------------------------+-----------------------------+

Whilst these results may be acceptable in some situations suppose you instead wanted the product descriptions sorted in alphabetical order. This is where the ORDER BY keywords come into play. The syntax for using ORDER BY is as follows:

SELECT column(s) FROM table name ORDER By column name

To continue with our example we could, therefore, sort the product descriptions as follows:

SELECT prod_desc FROM product ORDER BY prod_desc;

In this case, the results will be sorted according to the prod_desc column:

+-----------------------------+
| prod_desc                   |
+-----------------------------+
| CD Writer                   |
| Cordless Mouse              |
| Desktop PC                  |
| Ergonmoc Keyboard           |
| Mobile Phone                |
| Optical Mouse               |
| Portable Music/Movie Player |
| SATA Disk Drive             |
| Smart Phone                 |
| Wireless Ethernet Bridge    |
+-----------------------------+