ORDER BY - Caché SQL Reference - Caché & Ensemble 2018.1

ordering-item	A literal or scalar expression that determines the sort order. A column name or other column reference. An ORDER BY clause can contain one or more ordering items. Multiple items are separated by commas.
ASC DESC	Optional — Sort in either ascending order (ASC), or descending order (DESC). The default is ascending order.

The ORDER BY clause sorts the records in a query result set by the data values of a specified column or a comma-separated sequence of columns. This statement operates on a single result set, either from a SELECT statement or from a UNION of multiple SELECT statements.

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. An ORDER BY clause can specify any combination of these. You can specify an expression in the select-item list by column alias or column number. If the first character of the ordering-item is a number, Caché 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.

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

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

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

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 exists, the ORDER BY clause generates an 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 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.

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

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. You can specify a column number as an integer, or as any number. Standard truncation rules apply to resolve to an integer; for example, 1.99 resolves by truncation to 1.

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

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.

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

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

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.

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

sorts the data values of the third-listed item (C) in the SELECT clause list in ascending order; within this, it sorts the seventh-listed item (J) values in descending order; within this, it sorts the first-listed item (A) values in ascending order.

If you have specified a non-default NLS 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 CACHETEMP and process-private globals. For further details, refer to “SQL Collation and NLS Collations” in Using Caché SQL.

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).

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.

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 error such as:

Error: [SQLCODE: <-400>:<Fatal error occurred>] [Cache Error: <<SUBSCRIPT>%0ABMod+7^CacheSql4 ^||%sql.temp(36,0,"")>] 
[Details: <ServerLoop - Query Fetch>]  
[%msg: <Unexpected error occurred: <SUBSCRIPT>%0ABMod+7^CacheSql4 ^||%sql.temp(36,0,"")>]

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 {
  ZNSPACE "SAMPLES"
  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
  }

  TRY {
  ZNSPACE "SAMPLES"
  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.

The following two examples show different ways of specifying sort columns in an ORDER BY clause. The following two queries are equivalent; the first uses column names as sort items, the second uses column numbers (the sequence number of the items in the select-item list):

SELECT Name,Age,Home_State
FROM Sample.Person
ORDER BY Home_State,Age DESC

SELECT Name,Age,Home_State
FROM Sample.Person
ORDER BY 3,2 DESC

The following example sorts by a field containing Caché list data. Because a Caché list is an encoded character string that begins with formatting characters, this example uses $LISTTOSTRING to sort by the actual field data value, rather than the list element encoding:

SELECT Name,FavoriteColors
FROM Sample.Person
WHERE FavoriteColors IS NOT NULL
ORDER BY $LISTTOSTRING(FavoriteColors)

Dynamic SQL can use an input parameter to supply a literal value to an ORDER BY clause; it cannot use an input parameter to supply a field name, field alias, field number, or collation keyword. The following Dynamic SQL example uses an input parameter to sort result set records by first name:

  ZNSPACE "SAMPLES"
  SET myquery = 4
  SET myquery(1) = "SELECT TOP ? Name,Age,"
  SET myquery(2) = "CURRENT_DATE AS Today"
  SET myquery(3) = "FROM Sample.Person WHERE Age > ?"
  SET myquery(4) = "ORDER BY $PIECE(Name,',',?)"
  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(10,60,2)
  DO rset.%Display()
  WRITE !,"%Display SQLCODE=",rset.%SQLCODE

  SET topnum=10,agemin=60,firstname=2
  &sql(DECLARE pCursor CURSOR FOR
       SELECT TOP :topnum Name,Age,CURRENT_DATE AS Today
       INTO :name,:years,:today FROM Sample.Person
       WHERE Age > :agemin
       ORDER BY $PIECE(Name,',',:firstname) )
  &sql(OPEN pCursor)
       QUIT:(SQLCODE'=0)
  FOR { &sql(FETCH pCursor)
        QUIT:SQLCODE
        WRITE "Name=",name," Age=",years," today=",today,!
      }
  &sql(CLOSE pCursor)