ORDER BY

Specifying Sort Columns

You can specify a single column on which to sort, or multiple columns as a comma-separated list. Sorting is done by the first listed column, then within that column by the second listed column, and so on.

Columns can be specified by column name, column alias, or column number. ORDER BY is not limited to field values. You can specify any select-item by column alias or column number, including an aggregate function or an expression. You cannot, however, specify a column name default such as Expression_3; instead specify the select-item column number, 3 in this case.

An ORDER BY clause can specify any combination of column names, column aliases, and select-item column numbers. If the first character of the ordering-item is a number, InterSystems IRIS assumes you are specifying a column number. Otherwise a column name or column alias is assumed. Note that column names and column aliases are not case-sensitive.

With few exceptions, an ordering-item must be specified as a literal. If an ordering-item cannot be parsed as either a valid identifier (column name or column alias) or parsed as an unsigned integer (column number), that ordering-item is ignored and ORDER BY execution proceeds to the next ordering-item in the comma-separated list. Some examples of ignored ordering-item values are a Dynamic SQL ? input parameter or an Embedded SQL :var host variable, a subquery, an expression that resolves to a number, a signed number, or a number enclosed in parentheses.

Column Name

A column name can be specified as a literal. In some cases, an expression that operates upon a column name can be used as an ordering-item. You cannot use a variable or other expression that provides a column name as a string.

The following ORDER BY clause sorts by column names:

SELECT Name,Home_State,DOB
FROM Sample.Person
ORDER BY Home_State,Name

You can sort by column name whether or not the sort column is in the select-item list. (For obvious reasons, you cannot sort by column alias or column number unless the sort column is in the select-item list.) The following example returns the same records in the same order as the previous example:

SELECT Name,DOB
FROM Sample.Person
ORDER BY Home_State,Name

If the ordering-item is not an existing column name (or column alias) in the specified table, an SQLCODE –29 error is issued.

You can sort by the RowID value even if the RowID is private and not listed in the select-item list. You should specify the %ID pseudo-column name as the ordering-item, rather than the actual RowID field name. If the query contains a TOP clause, sorting by RowID changes which rows are selected by the TOP clause. For example, if a table has 100 rows (with sequential RowIDs), SELECT TOP 5 %ID FROM Table ORDER BY %ID returns RowIDs 1, 2, 3, 4, 5; SELECT TOP 5 %ID FROM Table ORDER BY %ID DESC returns RowIDs 100, 99, 98, 97, 96.

An ORDER BY clause can specify a table name or table alias as part of the ordering-item:

SELECT P.Name AS People,E.Name As Employees
FROM Sample.Person AS P,Sample.Employee AS E 
ORDER BY P.Name

An ORDER BY clause can use arrow syntax (–>) operator to specify a field in a table that is not the base table:

SELECT Name,Company->Name AS CompName
FROM Sample.Employee ORDER BY Company->Name,Name

For further details, refer to Implicit Joins in Using InterSystems SQL.

Column Alias

A column alias must be specified as a literal. You cannot specify a column alias in an expression, or supply it using a variable.

The following ORDER BY clause sorts by column alias:

SELECT Name,Home_State AS HS,DOB
FROM Sample.Person
ORDER BY HS,Name

A column alias can be the same as a column name (though this is not recommended). If column aliases are provided, ORDER BY first references column alias and then references any unaliased column names. If ambiguity between a column alias and a non-aliased column name exists, the ORDER BY clause generates an SQLCODE –24 error. However, if a column alias is the same as an aliased column name, this apparent ambiguity does not generate an error, but can produce unexpected results. This is shown in the following example:

SELECT Name AS Moniker,Home_City AS Name
FROM Sample.Person
ORDER BY Name

Because aliases are referenced first, this example orders the data by Home_City. This is probably not what was intended. If the Name column was not aliased, an SQLCODE –24 error would occur. If Home_City was given a different alias, ORDER BY would find no match on aliases, and would then check column names; it would order by the Name column.

You can use a column alias to sort by an expression in the select-item list, as shown in the following example:

SELECT Name,Age,$PIECE(AVG(Age)-Age,'.',1) AS AgeDev
FROM Sample.Employee ORDER BY AgeDev,Name

Column Number

A column number must be specified as an unsigned numeric literal. You cannot specify a column number as a variable or the result of an expression. You cannot enclose a column number in parentheses. Integer truncation rules apply to resolve a non-integer value to an integer; for example, 1.99 resolves to 1.

The following ORDER BY clause sorts by column number (the numeric sequence of the retrieved columns, as specified in the SELECT select-item list):

SELECT Name,Home_State,DOB
FROM Sample.Person
ORDER BY 2,1

Column numbers refer to the position in the SELECT clause list. They do not refer to the positions of columns in the table itself. However, you can sort SELECT * results by column number; if the RowID is public, it counts as column 1, if the RowID is hidden, it does not count as column 1.

Specifying a column number in ORDER BY that does not correspond to a SELECT list column results in an SQLCODE -5 error. ORDER BY 0 results in an SQLCODE -5 error.

You can use a column number to sort by an expression in the select-item list, as shown in the following example:

SELECT Name,Age,$PIECE(AVG(Age)-Age,'.',1)
FROM Sample.Employee ORDER BY 3,Name

Specifying Collation

Sorting is done in collation order. By default, ordering of string values is done based on the collation specified for the ordering-item field when it was created. Caché has a default string collation for each namespace; the initial collation default for string data type fields is SQLUPPER, which is not case-sensitive. Therefore, commonly, 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 field by applying a collation function to a ordering-item field name. 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. You can use the %PLUS collation function to order in integer sequence. However, the %PLUS collation function treats all non-numeric characters as 0.

Therefore, to properly sort mixed numeric strings in numeric sequence requires more than one ordering-item. For example, in Sample.Person a street address consists of an integer house number separated by a space from a street name. The street name consists of two parts separated by a space. Compare the following two examples. The first example sorts street addresses in character collation sequence:

SELECT Name,Home_Street FROM Sample.Person
ORDER BY Home_Street

The second example sorts the house number in integer sequence and the street name in character collation sequence:

SELECT Name,Home_Street FROM Sample.Person
ORDER BY $PIECE(%PLUS(Home_Street),' ',1),$PIECE(Home_Street,' ',2),$PIECE(Home_Street,' ',3)

Note that this example only works with a column name, not with a column alias or a column number.

ASC and DESC

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, as shown in the following example:

SELECT A,B,C,M,E,X,J
FROM LetterTable
ORDER BY 3,7 DESC,1 ASC

This example 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.

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.

Restrictions

If your SELECT query specifies an ORDER BY clause, the resulting data is not updateable. Thus, if you specify a subsequent DECLARE CURSOR FOR UPDATE statement, the FOR UPDATE clause is ignored, and the cursor is declared read-only.

If the ORDER BY applies to a UNION, an ordering item must be a number or a simple column name. It cannot be an expression. If a column name is used, it refers to result columns as they are named in the first SELECT list of the UNION.

When used in a subquery, an ORDER BY clause must be paired with a TOP clause. This may be a TOP ALL clause. For example, the following FROM clause subquery is not valid: (SELECT DISTINCT age FROM table1 ORDER BY age); however, the following FROM clause subquery is valid: (SELECT DISTINCT TOP ALL age FROM table1 ORDER BY age).

Cached Queries

Each literal value used in an ORDER BY clause generates a different cached query. Literal substitution is not performed on ORDER BY literals. This is because ORDER BY can use an integer to specify a column number. Changing this integer would result in a fundamentally different query.

ORDER BY and Long Global References

An ORDER BY ordering-item value should not exceed (approximately) between 400 and 500 characters, depending on the number of ordering-items and other factors. If an ordering-item value exceeds this maximum length, running a query with an ORDER BY clause may result in an SQLCODE -400 fatal error. This occurs because of a limitation in the maximum encoded length of a global reference, which is a fixed Caché system limit. To prevent this problem, use a truncation length in the collation setting for the field that is the basis of the ORDER BY clause. For example, the following query exceeds this limit:

  TRY {
  SET myquery = 3
    SET myquery(1) = "SELECT LocationCity,NarrativeSummary FROM Aviation.Event "
    SET myquery(2) = "WHERE LocationCity %Startswith 'Be' "
    SET myquery(3) = "ORDER BY NarrativeSummary"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
     IF rset.%SQLCODE=0 { WRITE !,"Executed query",! }
     ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)
            THROW badSQL }
  DO rset.%Display()
  WRITE !,"End of data"
  RETURN
  }
  CATCH exp { WRITE "In the CATCH block",!
              IF 1=exp.%IsA("%Exception.SQL") {
                WRITE "SQLCODE: ",exp.Code,!
                WRITE "Message: ",exp.Data,! }
              ELSE { WRITE "Not an SQL exception",! }
              RETURN
  }

Adding a collation function with a maxlen truncation length allows this program to execute successfully:

  TRY {
  SET myquery = 3
    SET myquery(1) = "SELECT LocationCity,NarrativeSummary FROM Aviation.Event "
    SET myquery(2) = "WHERE LocationCity %Startswith 'Be' "
    SET myquery(3) = "ORDER BY %SqlUpper(NarrativeSummary,400)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
     IF rset.%SQLCODE=0 { WRITE !,"Executed query",! }
     ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)
            THROW badSQL }
  DO rset.%Display()
  WRITE !,"End of data"
  RETURN
  }
  CATCH exp { WRITE "In the CATCH block",!
              IF 1=exp.%IsA("%Exception.SQL") {
                WRITE "SQLCODE: ",exp.Code,!
                WRITE "Message: ",exp.Data,! }
              ELSE { WRITE "Not an SQL exception",! }
              RETURN
  }

Caché truncates the collated value of the field at 400 characters. Remember that if the field contents are not unique within the first 400 characters, the data may be slightly misordered, but this is unlikely to occur. If this does occur, you can attempt to avoid displaying misordered data by using a larger value for truncation; however, if a value is too large, it will result in a <SUBSCRIPT> error.

Note also that the maximum length is for the entire encoded length of the global reference, including the length of the global name. It is not simply per subscript.