The order by clause allows you to sort query results by up to as many as 31 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, Adaptive Server 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). Adaptive Server 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.
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 Adaptive Server. The basic choices are binary, dictionary order, and case-insensitive. sp_helpsort displays the sort order for your server. See Chapter 9, “Configuring Character Sets, Sort Orders, and Languages” in the System Administration Guide: Volume 1.
Limitations Adaptive Server does not allow subqueries or variables in the order by list.
You cannot use order by on text, unitext, or image columns.
