ORDER BY (SQL)
Synopsis
ORDER BY orderItem
ORDER BY orderItem [ASC | DESC]
ORDER BY orderItem [ASC | DESC], orderItem2 [ASC | DESC]
Description
ORDER BY sorts the rows of a query result set by one or more specified ordering items, typically columns. Specify ORDER BY as the last clause in a SELECT statement, after the FROM, WHERE, GROUP BY, and HAVING clauses. For example:
SELECT Name, AVG(Age) AS AvgAge, Home_State
FROM Sample.Person
GROUP BY Home_State
ORDER BY AvgAge
-
ORDER BY orderItem sorts the rows of a query result set by the values of the specified order item, such as a column. The rows are returned in ascending order.
This statement returns the queried rows sorted by the Home_State column in ascending order.
SELECT Name,Age,Home_State FROM Sample.Person ORDER BY Home_State
Example: Sort By Column Name
-
ORDER BY orderItem [ASC | DESC] sorts the values in either ascending order (ASC) or descending order (DESC).
This statement returns the queried rows sorted by the Home_State column in descending order.
SELECT Name,Age,Home_State FROM Sample.Person ORDER BY Home_State DESC
Example: Using a TOP Clause with ORDER BY
-
ORDER BY orderItem [ASC | DESC], orderItem2 [ASC | DESC] sorts the values sequentially by one or more order items.
This statement returns the queried rows sorted first by the Home_State column in ascending order, then sorted by the Age column in descending order.
SELECT Name,Age,Home_State FROM Sample.Person ORDER BY Home_State,Age DESC
Examples:
The ORDER BY clause is applied after the execution of window functions in the SELECT list (including a window function’s own ORDER BY clause). Therefore, the values returned by a window function are not affected by the SELECT query’s ORDER BY clause.
If you omit the ORDER BY clause, the returned row order is unspecified and can differ with each statement execution.
ORDER BY sorts rows by the Logical (internal storage) data value, regardless of the current Select Mode setting. For more details on how ORDER BY sorts rows, see ORDER BY Collation.
Arguments
orderItem
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.
Examples
Sort By Column Name
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
Sort By Column Alias
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
Sort By Column Number
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
Using a TOP Clause with ORDER BY
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
Sort Based on List Data
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)
Sort Items Based on Host Variable Values
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.
Sort Items Using Dynamic SQL and Embedded SQL
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)
Limitations
-
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, this query is not valid because it uses a DISTINCT clause in the sorted subquery:
SELECT Name FROM Sample.Person WHERE Name = (SELECT DISTINCT Name FROM Sample.Employee ORDER BY Title)
The query is valid because it uses TOP ALL in the sorted subquery instead:
SELECT Name FROM Sample.Person WHERE Name = (SELECT TOP ALL Name FROM Sample.Employee ORDER BY Title)
-
Running a query with an orderItem value that exceeds 400 characters can result in an SQL -400 fatal error, which can lead to other errors such as <SUBSCRIPT>. This occurs because of a limitation in the maximum encoded length of a global reference, which is a fixed InterSystems IRIS 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, suppose the NarrativeSummary column of this query exceeds 400 characters:
SELECT LocationCity,NarrativeSummary FROM Aviation.Event WHERE LocationCity %STARTSWITH 'Be' ORDER BY NarrativeSummary
Adding a collation function with a maxlen truncation length allows this query to execute successfully.
SELECT LocationCity,NarrativeSummary FROM Aviation.Event WHERE LocationCity %STARTSWITH 'Be' ORDER BY %SQLUPPER(NarrativeSummary,400)
Performance
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.
More About
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