ORDER BY Clause

The ORDER BY clause specifies the order of rows retrieved by a fullselect. It sorts the rows of a query result according to the data values contained in a sort column (or columns). Sort columns are specified in the ORDER BY clause using either column names or column numbers separated by commas. Columns can also be named using the AS Clause (see SELECT Clause). The first column specified in the ORDER BY clause is the primary sort column. Output rows are ordered first according to the value in the primary sort column. If duplicate values occur in the primary sort column, the secondary sort column values are used to provide the final ordering.

The actual row order of the results depends on the data type of the column being sorted. Character columns are ordered according to the sort sequence specified during installation of the XDB Server (the default is a case insensitive ASCII sequence). Optional ordering parameters ASC (ascending) and DESC (descending) may be specified for each sort column.

When appearing in an SQL query statement, the ORDER BY clause is always the very last clause in the statement. Only one ORDER BY clause is permitted per query. ORDER BY clauses are not permitted in queries used to define views.

Please note that the ORDER BY clause only sorts the row output of query results, not the rows of the base table. To physically sort rows of data values in a base table, issue a CREATE table statement (with the same column configuration as the original table) combined with a SELECT query and ORDER BY clause. This single SQL command statement reproduces the original base table with the rows arranged according to the ORDER BY clause. The original base table remains unchanged.

Syntax

The ORDER BY clause format appears below:

ORDER BY sort-list

where:

sort-list Identifies up to 750 sort column names or numbers. The row order of query results can then be arranged according to the values of the sort column (or columns). Items in the sort list must be separated by commas, and may include an ordering parameter -- ASC (ascending) and DESC (descending) -- for each column name in the list. The default ordering parameter is ASC.