12.6 The ORDER BY Clause of the SQL Statement

The ORDER BY clause in a SQL statement is used to sort or rearrange the order in which the rows will appear in a query result. You can sort the rows of a query result in ascending or descending order based on the values of the fields included in the ORDER BY clause. Note that the ORDER BY clause does not remove any rows from the query result—it just rearranges them.

Ascending order implies arranging the rows from smallest to largest for numbers, alphabetically for names or words, or chronologically for dates. When you use an ORDER BY clause in a query, it is assumed that you would like the rows sorted in ascending order. Because of this, you do not need to specify that you wish to arrange the rows in ascending order in the ORDER BY clause. Suppose you want a list of members from the member table arranged alphabetically by last name. The results of this query are shown in Figure 12.14, and the syntax for this query is given below.

SELECT first_name, last_name FROM Member ORDER BY last_name

You may also wish to sort the results of a query in descending order. Descending order implies arranging the rows from largest to smallest for numbers, in reverse alphabetical order for names or words, or reverse chronological order for dates. To specify that you wish the rows of a query result to be sorted in descending order, you add the keyword DESC to the ORDER BY clause. For example, if you wanted the same list of members to be arranged in reverse alphabetical (Z to A) order by last name, you would use the syntax below. The results of this query are on Figure 12.15.

SELECT first_name, last_name FROM Member ORDER BY last_name DESC

You can also sort a query result by more than one field at the same time. When this is done, the first level of sorting will be done with the first field specified in the ORDER BY clause. The query will then be sorted by each subsequent field in the ORDER BY clause in the order that they appear in the clause. The fields are specified using a list of field names separated by commas in the ORDER BY clause.

Suppose we want to see the members of the club arranged by their position first in reverse alphabetical order (descending) and then by their last_name in alphabetical order. The syntax for this SQL statement is below. Notice that the DESC keyword appears next to the position field in the ORDER BY clause because that is the field we wish to have sorted in descending order.

SELECT first_name, last_name, position FROM Member ORDER BY position DESC, last_name

The results of this query appear in Figure 12.16. Notice that the rows of the results table are arranged by position in reverse alphabetical order. This is the primary sorting field. When the members’ positions are the same, the members are arranged in alphabetical order by their last name. This is the secondary sorting field. The secondary sorting field will only apply when the values of the primary sorting field repeat.

Additional instructions and examples for the ORDER BY clause can be found on data.world at Basic Concepts: ORDER BY.