Sorting Data Retrieved from a MySQL Database
| Previous | Table of Contents | Next |
| Retrieving Data From a MySQL Database | Using WHERE to Filter MySQL Data |
| Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99
|
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 from a 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 though 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 surprising 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;
This 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 a 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.
<google>BUY_MYSQL_BOTTOM</google>
| Previous | Table of Contents | Next |
| Retrieving Data From a MySQL Database | Using WHERE to Filter MySQL Data |
