ORDER BY Collation
Sorting is done in collation order. By default, the ordering of string values is done based on the collation specified for the ORDER BY orderItem column when it was created. If your InterSystems IRIS namespace uses the default string collation of SQLUPPER, then ORDER BY collation is not case-sensitive.
Ordering of numeric data type fields is done based on numeric collation. For expressions, the default collation is EXACT.
You can override the default collation for a column by applying a collation function. For example: ORDER BY %EXACT(Name). You cannot apply a collation function to a column alias. Attempting to do so generates an SQLCODE -29 error.
The default ascending collation sequence considers NULL to be the lowest value, followed by the empty string (''). ORDER BY does not distinguish between the empty string and strings that consist only of blank spaces.
If the collation specified for a column is alphanumeric, leading numbers are sorted in character collation sequence, not integer sequence. To order in integer sequence, you can use the %PLUS collation function, but this function treats all non-numeric characters as 0.
To properly sort mixed numeric strings in numeric sequence, you must specify more than one ORDER BY orderItem. Consider a Home_Street column that has this format:
Number StreetName StreetType
Number is an integer house number. StreetName and StreetType are strings that combine to form the full street name, such as "Elm Street".
This statement sorts street addresses in character collation sequence.
SELECT Name,Home_Street FROM Sample.Person
ORDER BY Home_Street
This statement sorts the house number in integer sequence and the street name in character collation sequence. This statement contains an expression and works only with a column name, not a column alias or column number.
SELECT Name,Home_Street FROM Sample.Person
ORDER BY $PIECE(%PLUS(Home_Street),' ',1),$PIECE(Home_Street,' ',2),$PIECE(Home_Street,' ',3)
ASC and DESC Collation
Sorting can be specified for each column in ascending or descending collation sequence order, as specified by the optional ASC (ascending) or DESC (descending) keyword following the column identifier. If ASC or DESC is not specified, ORDER BY sorts that column in ascending order. You cannot specify the ASC or DESC keyword using a Dynamic SQL ? input parameter or an Embedded SQL :var host variable.
NULL is always the lowest value in ASC sequence and the highest value in DESC sequence.
Multiple comma-separated ORDER BY values specify a hierarchy of sort operations. For example, this statement sorts the data values of the third-listed item (C) in the SELECT clause list in ascending order; within this sequence, it sorts the seventh-listed item (J) values in descending order; within this, it sorts the first-listed item (A) values in ascending order.
SELECT A,B,C,M,E,X,J
FROM LetterTable
ORDER BY 3,7 DESC,1 ASC
Duplicate columns in the list of ORDER BY values have no effect. This is because the second sort is within the order of the first sort. For example, ORDER BY Name ASC, Name DESC sorts the Name column in ascending order.
NLS Collation
If you have specified a non-default NLS (National Language Support) collation, you must make sure that all collations are aligned and use the exact same national collation sequence. This includes not only globals used by the tables, but also globals used for indexes, in temporary files such as in IRISTEMP and process-private globals. For more details, see SQL Collation and NLS Collations