ORDER BY clause

The ORDER BY clause is an optional element of several statements, expressions, and subqueries.

It can be an element of the following:

It can also be used in an INSERT statement or a CREATE VIEW statement.

An ORDER BY clause allows you to specify the order in which rows appear in the result set. In subqueries, the ORDER BY clause is meaningless unless it is accompanied by one or both of the result offset and fetch first clauses or in conjunction with the ROW_NUMBER function, since there is no guarantee that the order is retained in the outer result set. It is permissible to combine ORDER BY on the outer query with ORDER BY in subqueries.

Syntax

ORDER BY { columnName | columnPosition | expression }
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
    [ , columnName | columnPosition | expression 
        [ ASC | DESC ]
        [ NULLS FIRST | NULLS LAST ]
    ]*

columnName: Refers to the names visible from the selectItems in the underlying query of the SELECT statement. The columnName that you specify in the ORDER BY clause does not need to be the SELECT list.
columnPosition: An integer that identifies the number of the column in the selectItems in the underlying query of the SELECT statement. The columnPosition must be greater than 0 and not greater than the number of columns in the result table. In other words, if you want to order by a column, that column must be specified in the SELECT list.
expression: A sort key expression, such as numeric, string, and datetime expressions. An expression can also be a row value expression such as a scalarSubquery or case expression.
ASC: Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.
DESC: Specifies that the results should be returned in descending order.
NULLS FIRST: Specifies that NULL values should be returned before non-NULL values.
NULLS LAST: Specifies that NULL values should be returned after non-NULL values.

Notes

If SELECT DISTINCT is specified or if the SELECT statement contains a GROUP BY clause, the ORDER BY columns must be in the SELECT list.
An ORDER BY clause prevents a SELECT statement from being an updatable cursor. For more information, see Requirements for updatable cursors and updatable ResultSets.
If the null ordering is not specified then the handling of the null values is:
- NULLS LAST if the sort is ASC
- NULLS FIRST if the sort is DESC
If neither ascending nor descending order is specified, and the null ordering is also not specified, then both defaults are used and thus the order will be ascending with NULLS LAST.

Example using a correlation name

You can sort the result set by a correlation name, if the correlation name is specified in the select list. For example, to return from the CITIES database all of the entries in the CITY_NAME and COUNTRY columns, where the COUNTRY column has the correlation name NATION, you specify this SELECT statement:

SELECT CITY_NAME, COUNTRY AS NATION 
    FROM CITIES 
    ORDER BY NATION

Example using a numeric expression

You can sort the result set by a numeric expression, for example:

SELECT name, salary, bonus FROM employee 
   ORDER BY salary+bonus

In this example, the salary and bonus columns are DECIMAL data types.

Example using a function

You can sort the result set by invoking a function, for example:

SELECT i, len FROM measures 
   ORDER BY sin(i)

Example specifying null ordering

You can specify the position of NULL values using the null ordering specification:

SELECT * FROM t1 ORDER BY c1 DESC NULLS LAST