Chapter 3: How to retrieve data from a single table

In this chapter, you’ll learn how to code SELECT statements that retrieve data from a single table.

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

Figure 3-17 presents three more techniques 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 VendorCity, VendorState, and VendorZipCode columns. Within the Address column, the result set is also sorted by the VendorName 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 VendorContactLName column concatenated with the VendorContactFName column. Here, neither of these columns is included in the SELECT clause. Although SQL Server allows this seldom-used coding technique, many other database systems do not.

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, you should avoid using this technique.

An ORDER BY clause that uses an alias

SELECT VendorName,
VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address
FROM Vendors
ORDER BY Address, VendorName

An ORDER BY clause that uses an expression

SELECT VendorName,
VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address
FROM Vendors
ORDER BY VendorContactLName + VendorContactFName

An ORDER BY clause that uses column positions

SELECT VendorName,
VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address
FROM Vendors
ORDER BY 2, 1

Description

• The ORDER BY clause can include a column alias that's specified in the SELECT clause.
• 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.
• 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-17 How to sort a result set by an alias, an expression, or a column number