Sorting Results (ORDER BY Clause)

Sorting Results (ORDER BY Clause)
In general, the rows of an SQL query result table are not arranged in any particular order
(although some DBMSs may use a default ordering based, for example, on a primary key).
However, we can ensure the results of a query are sorted using the ORDER BY clause in
the SELECT statement. The ORDER BY clause consists of a list of column identifiers
that the result is to be sorted on, separated by commas. A column identifier may be either
a column name or a column number† that identifies an element of the SELECT list by
its position within the list, 1 being the first (left-most) element in the list, 2 the second
element in the list, and so on. Column numbers could be used if the column to be sorted
on is an expression and no AS clause is specified to assign the column a name that can subsequently
be referenced. The ORDER BY clause allows the retrieved rows to be ordered
in ascending (ASC) or descending (DESC) order on any column or combination of
columns, regardless of whether that column appears in the result. However, some dialects
insist that the ORDER BY elements appear in the SELECT list. In either case, the ORDER
BY clause must always be the last clause of the SELECT statement.
columns, regardless of whether that column appears in the result. However, some dialects
insist that the ORDER BY elements appear in the SELECT list. In either case, the ORDER
BY clause must always be the last clause of the SELECT statement.
Example 5.11 Single-column ordering
Produce a list of salaries for all staff, arranged in descending order of salary.
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;
This example is very similar to Example 5.2. The difference in this case is that the output
is to be arranged in descending order of salary. This is achieved by adding the ORDER BY
clause to the end of the SELECT statement, specifying salary as the column to be sorted, and
DESC to indicate that the order is to be descending. In this case, we get the result table
shown in Table 5.11. Note that we could have expressed the ORDER BY clause as: ORDER
BY 4 DESC, with the 4 relating to the fourth column name in the SELECT list, namely salary.
It is possible to include more than one element in the ORDER BY clause. The major
sort key determines the overall order of the result table. In Example 5.11, the major sort
key is salary. If the values of the major sort key are unique, there is no need for additional
keys to control the sort. However, if the values of the major sort key are not unique, there
may be multiple rows in the result table with the same value for the major sort key. In this
case, it may be desirable to order rows with the same value for the major sort key by some
additional sort key. If a second element appears in the ORDER BY clause, it is called a
minor sort key.
Example 5.12 Multiple column ordering
Produce an abbreviated list of properties arranged in order of property type.
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type;
In this case we get the result table shown in Table 5.12(a).
Table 5.12(a) Result table for Example 5.12
with one sort key.
There are four flats in this list. As we did not specify any minor sort key, the system
arranges these rows in any order it chooses. To arrange the properties in order of rent, we
specify a minor order, as follows:
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type, rent DESC;
Now, the result is ordered first by property type, in ascending alphabetic order (ASC being
the default setting), and within property type, in descending order of rent. In this case, we
get the result table shown in Table 5.12(b).
The ISO standard specifies that nulls in a column or expression sorted with ORDER BY
should be treated as either less than all non-null values or greater than all non-null values.
The choice is left to the DBMS implementor.