order by clause


Returns query results in the specified columns in sorted order.


[Start of select statement]
[order by
	{[table_name.| view_name.]
		column_name | select_list_number | expression}
		[asc | desc] 
	[,{[table_name.| view_name.]
		column_name | select_list_number | expression}
		[asc | desc]]...]
[End of select statement]


order by

sorts the results by columns.


sorts the results in ascending order. If you do not specify asc or desc, asc is assumed.


sorts the results in descending order.


Example 1

Selects the titles whose price is greater than $19.99 and lists them with the titles in alphabetical order:

select title, type, price 
from titles 
where price > $19.99 
order by title
        type         price
         ------------ -------------------------
But Is It User Friendly?
         popular_comp                    22.95
Computer Phobic and Non-Phobic Individuals: Behavior Variations
          psychology                     21.59
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
         trad_cook                       20.95
Secrets of Silicon Valley
         popular_comp                   20.00

Example 2

Lists the books from the titles table, in descending alphabetical order of the type, and calculates the average price and advance for each type:

select type, price, advance 
from titles 
order by type desc 
compute avg (price), avg (advance) by type

Example 3

Lists the title IDs from the titles table, with the advances divided by the total sales, ordered from the lowest calculated amount to the highest:

select title_id, advance/total_sales
from titles
order by advance/total_sales
 -------- ------------------------
 MC3026                     NULL
 PC9999                     NULL
 MC2222                       0.00
 TC4203                       0.26
 PS3333                       0.49
 BU2075                       0.54
 MC3021                       0.67
 PC1035                       0.80
 PS2091                       1.11
 PS7777                       1.20
 BU1032                       1.22
 BU7832                       1.22
 BU1111                       1.29
 PC8888                       1.95
 TC7777                       1.95
 PS1372                      18.67
 TC3218                      18.67
 PS2106                      54.05

Example 4

Lists book titles and types in order by the type, renaming the columns in the output:

select title as BookName, type as Type
from titles
order by Type



Collating sequences

Sort rules

When two rows have equivalent values in the Adaptive Server sort order, the following rules are used to order the rows:

Descending scans


ANSI SQL – Compliance level: Transact-SQL extension.

Specifying new column headings in the order by clause of a select statement when the union operator is used is a Transact-SQL extension.

See also

Commands compute clause, declare, group by and having clauses, select, where clause

System procedures sp_configure, sp_helpsort, sp_lock, sp_sysmon