How to Retrieve Data from a Single Table - How to Code the ORDER BY Clause
(Page 16 of 17 )
The ORDER BY clause specifies the sort order for the rows in a result set. In most cases, you'll use column names from the base table to specify the sort order as you saw in some of the examples earlier in this chapter. However, you can also use other techniques to sort the rows in a result set, as described in the topics that follow.
How to Sort a Result Set by a Column Name
Figure 16 presents the expanded syntax of the ORDER BY clause. As you can see, you can sort by one or more expressions in either ascending or descending sequence. This is illustrated by the three examples in this figure.
The first two examples show how to sort the rows in a result set by a single column. In the first example, the rows in the Vendors table are sorted in ascending sequence by the VendorName column. Notice that since ascending is the default sequence, the ASC keyword is omitted. In the second example, the rows are sorted by the VendorName column in descending sequence.
To sort by more then one column, you simply list the names in the ORDER BY clause separated by commas as shown in the third example. Here, the rows in the Vendors table are first sorted by the VendorState column in ascending sequence. Then, within each state, the rows are sorted by the VendorCity column in ascending sequence. Finally, within each city, the rows are sorted by the VendorName column in ascending sequence. This can be referred to as a nested sort because one sort is nested within another.
Although all of the columns in this example are sorted in ascending sequence, you should know that doesn't have to be the case. For example, I could have sorted by the VendorName column in descending sequence like this:
ORDER BY VendorState, VendorCity, VendorName DESC
Note that the DESC keyword in this example applies only to the VendorName column. The VendorState and VendorCity columns are still sorted in ascending sequence.
Figure 16: The expanded syntax of the ORDER BY clause
ORDER BY expression [ASC|DESC] [, expression [ASC|DESC]] ...
An ORDER BY clause that sorts by one column in ascending sequence:
SELECT VendorName,
VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address
FROM Vendors
ORDER BY VendorName
An ORDER BY clause that sorts by one column in descending sequence:
SELECT VendorName,
VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address
FROM Vendors
ORDER BY VendorName DESC
An ORDER BY clause that sorts by three columns:
SELECT VendorName,
VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address
FROM Vendors
ORDER BY VendorState, VendorCity, VendorName
Description
- The ORDER BY clause specifies how you want the rows in the result set sorted. You can sort by one or more columns, and you can sort each column in either ascending (ASC) or descending (DESC) sequence. ASC is the default.
- By default, in an ascending sort, nulls appear first in the sort sequence, followed by special characters, then numbers, then letters. Although you can change this sequence, that's beyond the scope of this book.
- You can sort by any column in the base table regardless of whether it's included in the SELECT clause. The exception is if the query includes the DISTINCT keyword. Then, you can only sort by columns included in the SELECT clause.
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Sort a Result Set by an Alias, an Expression, or a Column Number >>
More MS SQL Server Articles
More By Bryan Syverson
