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 nameYou 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 expressionYou 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 functionYou can sort the result
set by invoking a function, for example:SELECT i, len FROM measures
ORDER BY sin(i)