Sorting Database Columns With the SELECT Statement

How to sort a result set by an alias, an expression, or a column number

Figure 3-18 presents three more techniques that you can use to specify sort columns. First, you can use a column alias that’s defined in the SELECT clause. The first SELECT statement in this figure, for example, sorts by a column named address, which is an alias for the concatenation of the vendor_city, vendor_state, and vendor_zip_code columns. Within the address column, the result set is sorted by the vendor_name column.

You can also use an arithmetic or string expression in the ORDER BY clause, as illustrated by the second example in this figure. Here, the expression consists of the vendor_contact_last_name column concatenated with the vendor_contact_first_name column. Here, neither of these columns is included in the SELECT clause.

The last example in this figure shows how you can use column numbers to specify a sort order. To use this technique, you code the number that corresponds to the column of the result set, where 1 is the first column, 2 is the second column, and so on. In this example, the ORDER BY clause sorts the result set by the second column, which contains the concatenated address, then by the first column, which contains the vendor name. The result set returned by this statement is the same as the result set returned by the first statement.

Notice, however, that the statement that uses column numbers is more difficult to read because you have to look at the SELECT clause to see what columns the numbers refer to. In addition, if you add or remove columns from the SELECT clause, you may also have to change the ORDER BY clause to reflect the new column positions. As a result, we don’t recommend this coding technique.

An ORDER BY clause that uses an alias

SELECT vendor_name,
vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS address
FROM vendors
ORDER BY address, vendor_name

An ORDER BY clause that uses an expression

SELECT vendor_name,
vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS address
FROM vendors
ORDER BY vendor_contact_last_name || vendor_contact_first_name

An ORDER BY clause that uses column positions

SELECT vendor_name,
vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS address
FROM vendors
ORDER BY 2, 1

Description

1. The ORDER BY clause can include a column alias that’s specified in the SELECT clause.
   
2. The ORDER BY clause can include any valid expression. The expression can refer to any column in the base table, even if it isn’t included in the result set.
   
3. The ORDER BY clause can use numbers to specify the columns to use for sorting. In that case, 1 represents the first column in the result set, 2 represents the second column, and so on.

--------------------------------------------Figure 3-18 How to sort a result set by an alias, an expression, or a column number