The order by clause allows you to sort query results by one or more columns, up to 31. 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)
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)
You can use the position number of a column in a select list instead of the column name. Column names and select list numbers can be mixed. 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 could 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 or image columns.
Adaptive Server does not allow subqueries, aggregates, variables and constant expressions in the order by list.
With order by, null values come before all others.
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. The system procedure sp_helpsort displays the sort order for your server. See the Reference Manual for more information on sort orders.
