The ORDER BY Clause

An ORDER BY clause is used to sort the results for the query.

The ORDER BY clause follows both the WHERE clause and GROUP BY clause, if present. For details on the syntax of the SELECT statement, see The Syntax of the SELECT Statement. The syntax of the ORDER BY clause is: 
ORDER BY <ordering term> [ASC | DESC][, <ordering term> [ASC | DESC] ] . . .

An ordering term can be a column in the select list, an alias specified in the select list, or a column index number in the select list. Each ordering term can optionally be followed by ASC or DESC. ASC indicates that the query results must be sorted on the ordering term column in ascending order. DESC indicates that the query results must be sorted on the ordering term column in descending order. The default sort order is ascending.

TIBCO recommends using columns in the ORDER BY clause, which are columns of a primary or secondary index defined for the table. Using columns of a primary or secondary index increases the efficiency and speed of the query. ORDER BY clauses with columns that match the order of defined indexes exactly or match the order of defined indexes in the reverse order are usually more efficient than other types of ORDER BY clauses.

When the SELECT statement includes a WHERE clause, the ordering terms used in the ORDER BY clause must match the columns of the primary or secondary index selected for processing the WHERE clause to achieve the best performance.

If you do not use columns from a primary or secondary index in the ORDER BY clause, there is a per-query limit on the amount of allowed memory by default (128MB) that can be used to buffer results when attempting to order them.

Example

For example, there is an index defined for the table by the name of index1 that is indexed on lastname, firstname, and city. The following queries use index1 because the columns match the index column in the same order and the sorting is in a single direction (either in the ascending order or in the descending order): 
SELECT * FROM t1 ORDER BY lastname ASC, firstname ASC, city ASC

SELECT * FROM t1 ORDER BY lastname DESC, firstname DESC, city DESC
The following query cannot use index1 because some columns are sorted in the ascending order and some in the descending order: 
SELECT * FROM t1 ORDER BY lastname DESC, firstname ASC, city ASC

SELECT * FROM t1 ORDER BY lastname ASC, firstname DESC, city ASC
Related concepts