ORDER BY clause

ORDER BY clause The ORDER BY clause is an optional element of a SELECT statement. An ORDER BY clause allows you to specify the order in which rows appear in the ResultSet. ORDER BY clausesyntax ASCimplied in ORDER BY clause DESCoptional element of ORDER BY clause ORDER BY clauseaffecting cursors ORDER BY clausespecifying expressions SyntaxORDER BY { | ColumnPosition | Expression } [ ASC | DESC ] [ , | ColumnPosition | Expression [ ASC | DESC ] ] *

column-Name: Refers to the names visible from the SelectItems in the underlying query of the . The column-Name 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 . 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. Expression can also be a row value expression such as a scalar subquery 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.

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 . For example, if an INTEGER column contains integers, NULL is considered greater than 1 for purposes of sorting. In other words, NULL values are sorted high.

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+bonusIn 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)