Sort Query Results: the order by Clause

The order by clause allows you to sort query results by up to as many as 400 columns. Each sort is either ascending (asc) or descending (desc). If neither is specified, asc is the default.

The following query orders results by pub_id:
select pub_id, type, title_id 
from titles 
order by pub_id 
pub_id  type             title_id 
------  ------------     --------
0736    business         BU2075 
0736    psychology       PS2091 
0736    psychology       PS2106 
0736    psychology       PS3333 
0736    psychology       PS7777 
0877    UNDECIDED        MC3026 
0877    mod_cook         MC2222 
0877    mod_cook         MC3021 
0877    psychology       PS1372 
0877    trad_cook        TC3218 
0877    trad_cook        TC4203 
0877    trad_cook        TC7777 
1389    business         BU1032 
1389    business         BU1111 
1389    business         BU7832 
1389    popular_comp     PC1035 
1389    popular_comp     PC8888 
1389    popular_comp     PC9999 
 
(18 rows affected) 

Multiple Columns

If you name more than one column in the order by clause, SAP ASE nests the sorts. The following statement sorts the rows in the stores table first by stor_id in descending order, then by payterms (in ascending order, since desc is not specified), and finally by country (also ascending). SAP ASE sorts null values first within any group.

select stor_id, payterms, country
from stores
order by stor_id desc, payterms
stor_id payterms     country 
------- ------------ ------------ 
8042    Net 30       USA          
7896    Net 60       USA          
7131    Net 60       USA          
7067    Net 30       USA          
7066    Net 30       USA          
6380    Net 60       USA          
5023    Net 60       USA          

(7 rows affected)

Column Position Numbers

You can use the position number of a column in a select list instead of the column name. You can mix column names and select list numbers. Both of the following statements produce the same results as the preceding one.

select pub_id, type, title_id 
from titles 
order by 1 desc, 2, 3 
select pub_id, type, title_id 
from titles 
order by 1 desc, type, 3 

Most versions of SQL require that order by items appear in the select list, but Transact-SQL has no such restriction. You can order the results of the preceding query by title, although that column does not appear in the select list.

Note: You cannot use order by on text, unitext, or image columns.

Aggregate Functions

Aggregate functions are permitted in an order by clause, but they must follow a syntax that avoids ambiguity about which order by column is subject to the union expression. However, the name of columns in a union is derived from the first (leftmost) part of the union. This means that the order by clause uses only column names specified in the first part of the union.

For example, the following syntax works, because the column identified by the order by key is clearly specified:

select id, min(id) from tab
union
select id, max(id) from tab
ORDER BY 2

However, this example produces an error message:

select id+2 from sysobjects
union
select id+1 from sysobjects
order by id+1
------------
Msg 104, Level 15, State1:
Line 3:
Order-by items must appear in the select list if the statement contains set operators.

If you rearrange the statement by trading the union sides, it executes correctly:

select id+1 from sysobjects
union
select id+2 from sysobjects
order by id+1

Null Values

With order by, null values come before all others.

Mixed-Case Data

The effects of an order by clause on mixed-case data depend on the sort order installed on your SAP ASE. The basic choices are binary, dictionary order, and case-insensitive. sp_helpsort displays the sort order for your server. See System Administration Guide: Volume 1 > Configuring Character Sets, Sort Orders, and Languages.

Limitations

SAP ASE does not allow subqueries or variables in the order by list.

You cannot use order by on text, unitext, or image columns.