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 SELECTed data
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 | +-----------------------------+
It is not necessary to sort on the selected column when retrieving data. It is perfectly valid to sort on any column in a table, regardless of whether it is one of the columns referenced in a SELECT statement.
Sorting on Multiple Columns
In the previous examples we have used a single table column as the criteria for sorting data retrieved from a MySQL database. ORDER BY also provides a way to sort on multiple columns. The columns to be used for the sort are specified after the ORDER BY keywords and separated by commas. For example, to sort on a combination of the product name and product description in our table we would execute the following SQL statement:
SELECT * FROM product ORDER BY prod_name, prod_desc;
which results in the following data ordering:
+-----------+----------------------------+-----------------------------+ | prod_code | prod_name | prod_desc | +-----------+----------------------------+-----------------------------+ | 5 | Apple iPhone 8Gb | Smart Phone | | 8 | Apple iPod Touch | Portable Music/Movie Player | | 10 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge | | 1 | CD-RW Model 4543 | CD Writer | | 7 | Dell XPS 400 | Desktop PC | | 2 | EasyTech Mouse 7632 | Cordless Mouse | | 9 | Kensington Ci20 | Optical Mouse | | 4 | Microsoft 10-20 Keyboard | Ergonmoc Keyboard | | 6 | Moto Razr | Mobile Phone | | 3 | WildTech 250Gb 1700 | SATA Disk Drive | +-----------+----------------------------+-----------------------------+ 10 rows in set (0.00 sec)
Sorting Data in Descending Order
So far in this chapter we have sorted all our MySQL database queries in ascending order. Suppose, however, that visitor to your web site needs to view the prices for a list of items starting with the most expensive. Clearly the standard ORDER BY specification will not address this need. Fortunately SQL includes the DESC keyword to specify that the results of a SELECT statement are to be sorted in descending order. For example, to sort the product names in descending order:
mysql> SELECT prod_name FROM product ORDER BY prod_name DESC; +----------------------------+ | prod_name | +----------------------------+ | WildTech 250Gb 1700 | | Moto Razr | | Microsoft 10-20 Keyboard | | Kensington Ci20 | | EasyTech Mouse 7632 | | Dell XPS 400 | | CD-RW Model 4543 | | Buffalo AirStation Turbo G | | Apple iPod Touch | | Apple iPhone 8Gb | +----------------------------+ 10 rows in set (0.00 sec)
Summary
In this chapter we have looked at how to use the SELECT statement in conjunction with the ORDER BY and DESC keywords to retrieve and sort data from a MySQL database table. In the next chapter we explore ways to filter retrieved data using the WHERE keyword.
