ORDER BY clause

Allows ordering of the rows selected by the SELECT statement. Unless an ORDER BY clause is specified, the rows of the result set might be returned in an unpredictable order as determined by the access paths chosen and other decisions made by the query optimizer. The decisions made will be affected by the statistics generated from table and index data examined by the UPDATE STATISTICS command.

Syntax

ORDER BY {expr|posn}[ ASC | DESC ]
   [ , { expr|posn}[ ASC | DESC ] , ...]

Parameters

expr

Expression of one or more columns of the tables specified in the FROM clause of the SELECT statement.

posn

Integer column position of the columns selected by the SELECT statement.

ASC | DESC

Indicates whether to order by ascending order (ASC) or descending order. The default is ASC.

Notes

• The ORDER BY clause, if specified, should follow all other clauses of the SELECT statement.
• The selected rows are ordered on the basis of the first expr or posn. If the values are the same, then the second expr or posn is used in the ordering.
• A query expression can be followed by an optional ORDER BY clause. If the query expression contains set operators (such as, UNION), then the ORDER BY clause can specify column names, aliases, and positions in the following ways:
  • An alias from the first SELECT statement of the set operator query expression can be used as the ORDER BY key. In this case, the alias indicates the ordering based on the corresponding column from all the query expressions (SELECT statements) in the set operator.
  • A column name from the first SELECT statement of the set operator query expression can be explicitly used as an ORDER BY key. In this case, the column name indicates the ordering based on the corresponding column from all the query expressions (SELECT statements) in the set operator.
  • An ordinal position number can be specified as an ORDER BY key.
• A query expression is considered invalid if the ORDER BY clause:
  • Has an expression
  • Refers to a column name or alias that is not from the first SELECT statement
  • Has a position number that is greater than the number of columns projected

Examples

ORDER BY clause

The following example demonstrates the ORDER BY clause in the SELECT statement:

-- Produce a list of customers sorted by name.
SELECT Name, Address, City, State, PostalCode
     FROM Customer
     ORDER BY Name ;

ORDER BY clause in query expressions containing set operators

The following examples demonstrate the ORDER BY clause in query expressions containing set operators:

-- Produce a merged list of customers and suppliers with the column position specifying the sort key.
SELECT Name, Address, State, PostalCode
     FROM Customer
     UNION
     SELECT Name, Address, State, PostalCode
     FROM Supplier
     ORDER BY 1; 
-- Produce a merged list of customers and suppliers sorted by name.
SELECT Name, Address, State, PostalCode
     FROM Customer
     UNION
     SELECT Name, Address, State, PostalCode
     FROM Supplier
     ORDER BY Name; 
-- Produce a merged list of customers and suppliers sorted by name.
SELECT cust_name as Name, cust_address, cust_state, cust_postalcode
     FROM Customer
     UNION
     SELECT sup_name, sup_address, sup_state, sup_postalcode
     FROM Supplier
     ORDER BY Name;