ORDER BY (SQL)

An item, or comma-separated list of items, that specifies the order by which to sort the query result set. You can specify the items in orderItem as any combination of the following:

• The name of a column in the table. The column does not need to be specified in the SELECT list. Column names are not case-sensitive. Specifying a column name that is not in the table generates an SQLCODE -29 error.

• The alias of a column in the table. The alias must be specified in the SELECT list. Column aliases are not case-sensitive.

• The number of a column in the table, specified as an unsigned numeric literal. The number is based on the order of columns specified in the SELECT list. Specifying a column number that does not correspond to a SELECT list column results in an SQLCODE -5 error.

  If the first character of an orderItem is a number, InterSystems IRIS® assumes you are specifying a column number. Integer truncation rules apply to resolve a non-integer value to an integer. For example, 1.99 resolves to 1. Otherwise, it assumes orderItem is a column name or column alias.

  You cannot specify a column number as a variable or as the result of an expression. You cannot enclose a column number in parentheses.

• An expression evaluated on a column in the table, such as ORDER BY LENGTH(Name).

• A window function, such as ORDER BY ROW_NUMBER() OVER (PARTITION BY State).

• An aggregate function, provided that it is also specified in the SELECT list. Specifying an aggregate function only in the ORDER BY clause generates an SQLCODE -73 error.

With few exceptions, an orderItem must be specified as a literal. You cannot use a variable or other expression that provides a column name as a string. If an orderItem cannot be parsed as either a valid identifier (column name or column alias) or unsigned integer (column number), that orderItem is ignored and ORDER BY execution proceeds to the next orderItem in the comma-separated list. Some examples of ignored orderItem values include:

• Dynamic SQL ? input parameters

• Embedded SQL :var host variables

• Subqueries

• Expressions that resolve to a number

• Signed numbers

• Numbers enclosed in parentheses

If the orderItem property is a very long string or if you are attempting to use ORDER BY on multiple longer orderItems, InterSystems SQL will raise an error. To avoid this, you can define TRUNCATE collation on the relevant fields. In general, truncating at 128 characters is safe.

This statement 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 list. For example, this statement returns the same rows in the same order as the previous statement, even though its SELECT list does not include the Home_State column:

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

You can sort by the RowID value even if the RowID is private and not listed in the SELECT list. Specify the %ID pseudo-column name as the orderItem, rather than the actual RowID name. For example:

SELECT Name,DOB
FROM Sample.Person
ORDER BY %ID

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

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 the arrow syntax (–>) operator to specify a column in a table that is not the base table:

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

This statement sorts by column alias.

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

This statement sorts by an expression in the SELECT list that has an alias.

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

This statement sorts by column number, that is, the numeric sequence of the retrieved columns specified in the SELECT list. It sorts by Home_State (column 2), then by Name (column 1)

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

This statement sorts by an expression in the SELECT list using a column number.

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

When you sort SELECT * results by column number, if the RowID is public (default), it counts as column 1.

SELECT * FROM Sample.Person ORDER BY 1

If a SELECT statement specifies an ORDER BY and a TOP clause, the returned "top" rows are based on the order specified in the ORDER BY clause. For example, this statement returns the 5 rows from MyTable that have the highest age value, ordered from older to younger.

SELECT TOP 5 Name,Age FROM MyTable ORDER BY Age DESC

Sorting by RowID changes which rows are selected by the TOP clause. For example, consider a table that has 100 rows with sequential RowIDs. These statements returns rows 1, 2, 3, 4, 5 and rows 100, 99, 98, 97, 96, respectively.

SELECT TOP 5 %ID FROM MyTable ORDER BY %ID

SELECT TOP 5 %ID FROM MyTable ORDER BY %ID DESC

This statement sorts by a column containing InterSystems IRIS list data. Because an InterSystems IRIS list is an encoded character string that begins with formatting characters, this statement uses $LISTTOSTRING to sort by the actual column value, rather than the list element encoding:

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

You can use the CASE expression to define a general-purpose query that can be ordered based on a supplied host variable value. For example, this statement can order by either Name or Age, depending on the value of var:

SELECT Name,Age FROM Sample.Person ORDER BY 
CASE WHEN :var=1 then Name
     WHEN :var=2 then Age END

This statement specifies two CASE expressions. It orders by whichever case evaluates to true. If both cases evaluate to true, it orders by Country, and within Country by City:

SELECT Country,City FROM Sample.Person ORDER BY 
CASE WHEN :var1=1 then Country END,
     WHEN :var2=1 then City END

Specify the ASC and DESC argument after the CASE END keyword.

You must specify fields in a CASE expression by column name. Column aliases and column numbers are not permitted in this context.

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 column name, column alias, column number, or collation keyword. This Dynamic SQL example uses an input parameter to sort result set rows by first name:

  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

This cursor-based Embedded SQL example performs the same operation:

  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)

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.