Like the rows of a table in the database, the rows of query results are not arranged in any particular order. You can ask SQL to sort the results of a query by including the ORDER BY clause in the SELECT statement. The ORDER BY clause, shown in Figure 6-14, consists of the keywords ORDER BY, followed by a list of sort specifications separated by commas. For example, the results of this query are sorted on two columns, REGION and CITY:
Show the sales for each office, sorted in alphabetical order by region, and within each region by city.
The first sort specification (REGION) is the major sort key; those that follow (CITY, in this case) are progressively more minor sort keys, used as “tie breakers” when two rows of query results have the same values for the more major keys. Using the ORDER BY clause, you can request sorting in an ascending or descending sequence, and you can sort on any item in the select list of the query.
By default, SQL sorts data in ascending sequence. To request sorting in descending sequence, the keyword DESC is included in the sort specification, as in the next example:
List the offices, sorted in descending order by sales, so that the offices with the largest sales appear first.
As indicated in Figure 6-14, you can also use the keyword ASC to specify an ascending sort, but because that’s the default sorting sequence, the keyword is usually omitted.
If the column of query results to be used for sorting is a calculated column, it has no column name to be used in a sort specification. In this case, you must specify a column number instead of a column name, as in this example:
List the offices, sorted in descending order by sales performance, so that the offices with the best performance appear first.
These query results are sorted on the third column, which is the calculated difference between the SALES and TARGET for each office. By combining column numbers, column names, ascending sorts, and descending sorts, you can specify quite complex sorting of the query results, as in the following final example:
List the offices, sorted in alphabetical order by region, and within each region in descending order by sales performance.
The SQL2 standard allows you to control the sorting order used by the DBMS for each sort key. This can be important when working with international character sets or to insure portability between ASCII and EBCDIC character set systems. However, this area of the SQL2 specification is quite complex, and in practice, many SQL implementations either ignore sorting sequence issues or use their own proprietary scheme for user control of the sorting sequence.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.