How to code the ORDER BY clause - Oracle

The ORDER BY clause specifies the sort order for the rows in a result set. In most cases, you can 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 3-17 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 vendor_name column. Since ascending is the default sequence, the ASC keyword is omitted. In the second example, the rows are sorted by the vendor_name 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 vendor_state column in ascending sequence. Then, within each state, the rows are sorted by the vendor_city column in ascending sequence. Finally, within each city, the rows are sorted by the vendor_name 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, we could have sorted by the vendor_name column in descending sequence like this:

ORDER BY vendor_state, vendor_city, vendor_name DESC

Note that the DESC keyword in this example applies only to the vendor_name column. The vendor_state and vendor_city columns are still sorted in ascending sequence.

If you study the first example in this figure, you can see that capital letters come before lowercase letters in an ascending sort. As a result, “ASC Signs” comes before “Abbey Office Furnishings” in the result set. For some business applications, this is acceptable. But if it isn’t, you can use the LOWER function to convert the column to lowercase letters in the ORDER BY clause like this:

ORDER BY LOWER(vendor_name)

Then, the rows will be sorted in the correct alphabetical sequence. In chapter 8, you can learn more about this function.

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 vendor_name,
vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS address
FROM vendors
ORDER BY vendor_name

An ORDER BY clause that sorts by one column in descending sequence

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

An ORDER BY clause that sorts by three columns

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

Description

1. 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.
   
2. By default, in an ascending sort, special characters appear first in the sort sequence, followed by numbers, then by capital letters, then by lowercase letters, and then by null values. In a descending sort, this sequence is reversed.
   
3. With one exception, 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.

--------------------------------------------Figure 3-17 How to sort a result set by a column name

Please check back for the conclusion to this series.