BY clause to order the rows selected by a query. Sorting by position is useful in the following cases:
To order by a lengthy select list expression, you can specify its position in the
BYclause rather than duplicate the entire expression.
For compound queries containing set operators
BYclause must specify positions or aliases rather than explicit expressions. Also, the
BYclause can appear only in the last component query. The
BYclause orders all rows returned by the entire compound query.
The ordering method by which Oracle Database sorts character values for the
ORDER BY clause, also known as the collation, is determined for each
ORDER BY clause expression separately using the collation derivation rules.
If the determined collation of an expression is not the collation
BINARY, then the character values are compared linguistically. In this case, they are first transformed to collation keys and then compared like
RAW values. The collation keys are generated implicitly using the same method that the SQL function
NLSSORT uses. Generated collation keys are subject to the same restrictions that are described in "
NLSSORT". As a result of these restrictions, if the initialization parameter
MAX_STRING_SIZE is set to
STANDARD, two values may compare as linguistically equal if they do not differ in the prefix that was used to produce the collation key, even if they differ in the rest of the value. If the parameter's value is
EXTENDED, then the error "
ORA-12742: unable to create the collation key" may be reported under certain circumstances. See the links below for further information on the restrictions.