<< HAVING clause | The result offset and fetch first clauses >>

ColumnPosition

<< HAVING clause | The result offset and fetch first clauses >>

Derby Reference Manual

[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[ ,

column-Name

| ColumnPosition | Expression

[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
] *

column-Name

Refers to the names visible from the SelectItems in the underlying query of the

SELECT statement

. 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

SELECT statement

. 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

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.