|
Caché SQL Reference
ORDER BY
|
|
A SELECT clause that specifies the sorting of rows in a result set.
Synopsis
ORDER BY ordering-item [ASC | DESC]{,ordering-item [ASC | DESC] ...}
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.
ORDER BY sorts records by the Logical (internal storage) data value, regardless of the current
Select Mode setting.
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.
You cannot specify a column name, column alias, or column number using a Dynamic SQL ? input parameter or an Embedded SQL
:var host variable.
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. 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
For obvious reasons, you cannot sort by column alias or column number unless the sort column is in the
select-item list.
An
ORDER BY clause can use the
arrow syntax (–>) operator to specify a field in a table that is not the base table, as shown in the following example:
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.
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
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
Using a column number in
ORDER BY that does not correspond to a
SELECT list column results in an SQLCODE -5 error.
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.
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
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)
You can also use the
%MVR collation function to sort mixed numeric strings.
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.
This is shown in the following example:
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 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.
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 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
}
Adding a collation function with a
maxlen truncation length allows this program to execute successfully:
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.
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.
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
The following 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)
FOR { &sql(FETCH pCursor)
QUIT:SQLCODE
WRITE "Name=",name," Age=",years," today=",today,!
}
&sql(CLOSE pCursor)