You can sort the data you retrieve with a SELECT statement in ascending or descending order by adding an ORDER BY clause to the SELECT statement.
If you don't sort table information, it will usually be displayed in the order in which it was entered into the table. However, if the data is edited, the order may change. If the order of the information is important to you, make sure to explicitly order it in your SQL statement.
In the following task, you'll retrieve the id and class fields from the flightprefs table and then sort the retrieved data by the id value. Although it wouldn't normally be necessary to sort data when there are only four members in our travel club, as the club grows it will be useful to sort the data by last name or by id number to make it easier to find a specific member's information in the set of data we retrieve using a query.
To sort data using SQL:
Do one of the following:
- To start the MySQL Monitor in Windows, follow Steps 1 through 4 of "To create a database using MySQL in Windows," earlier in this chapter.
- To start MySQL in a Terminal window on a Mac, follow Steps 1 through 8 of "To create a database using MySQL on a Mac," earlier in this chapter.
At the mysql prompt, type use travelclub; and press Enter (Windows) or Return (Mac) to choose the travelclub database.
In the next step, you'll retrieve all the data from the id and class fields in the flightprefs table, and then sort it in ascending order by the id value.
At the prompt, type SELECT id, class FROM flightprefs ORDER BY id; and press Enter (Windows) or Return (Mac).
The id and class columns appear. The id column is in ascending order (Figure 5.15), which is the default.
Figure 5.15. Sorting a selection in ascending order.
At the prompt, type SELECT id, class FROM flightprefs ORDER BY id DESC; and press Enter (Windows) or Return (Mac).
The id and class columns appear, with the id column in descending order (Figure 5.16).
Figure 5.16. Sorting a selection in descending order.
At the prompt, do one of the following:
- If you're running Windows, type exit and press Enter to close the MySQL Monitor.
- If you're on a Mac, type exit and press Return to stop MySQL, type exit and press Return to log out, and then quit Terminal and close the Terminal window.