order by clause

Description

Returns query results in the specified columns in sorted order.

Syntax

[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]

Parameters

order by

sorts the results by columns.

asc

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

desc

sorts the results in descending order.

Examples

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
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
 title_id
 -------- ------------------------
 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

Usage


Restrictions


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

Standards

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