ORDER BY Clause

The ORDER BY clause sorts query results by specified columns or expressions.

This syntax fragment is part of the SELECT statement.
(1)
ORDER BY Clause

1  ORDER ? SIBLINGS1 BY + ,
2.1 
2.2.1 ?  table . column
2.2.1  %Expression2
2.1?  %Substring
2.2.1 select_number
2.2.1 display_label
2.2.1  %CASE expression3
2.2.1 4 ROWID
2.2.1 5 %OLAP window function6
1 
2.1! ASC
2.1?  DESC
1?  NULLS
2.1 FIRST
2.1 LAST7
Substring

1 4 [ first, last  ]8
Notes:
Element Description Restrictions Syntax
column Sort rows by value in this column None Identifier
display_label Temporary name for a column or for a column expression Must be unique among labels declared in the Projection clause Identifier
first, last First and last byte in column substring to sort the result set Integers; for BYTE, TEXT, and character data types only Literal Number
select_ number Ordinal position of a column in the select list of the Projection clause See GROUP BY Clause. Literal Number
table Name, synonym, or alias of the table or view containing column Must exist and must be specified in the FROM clause Identifier

The ORDER BY clause implies that the query returns more than one row. In SPL, the database server issues an error if you specify the ORDER BY clause without a FOREACH loop to process the returned rows individually within the SPL routine.

The following query specifies a derived table in the FROM clause whose rows are ordered by the col1 value, and declares vtab as the name of the derived table, and vcol as the name of its only column: 
SELECT vcol FROM 
   (SELECT FIRST 5 col1 FROM tab1 ORDER BY col1) vtab(vcol);

ORDER BY in NLSCASE INSENSITIVE databases

In databases created with the NLSCASE INSENSITIVE property, operations on columns and expressions of NCHAR or NVARCHAR data types make no distinction between upper case and lower case letters. For this reason, queries that include the ORDER BY clause might return rows in a sequence that disregard variants in letter case, if the column or expression are of NLS data types, and the data includes values that differ only in letter case.

If the data set includes letter case variants of the same string, these will be processed as duplicates, with case variants listed in their order of retrieval. For example, a set of NCHAR or NVARCHAR strings that were processed as duplicates might appear in this order:

gAMma
GAmma
GaMMa
gamma
GAMMA

For more information, see Duplicate rows in NLSCASE INSENSITIVE databases and NCHAR and NVARCHAR expressions in case-insensitive databases.