The ORDER BY clause is an optional element of the following:
- A SELECT statement
- A
- A VALUES expression
- A
- A
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
, 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.
SyntaxORDER BY { | ColumnPosition | Expression }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[ , | ColumnPosition | Expression
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
] *
- 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.
- 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 .
- 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 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)
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