order by clause

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 with a price that 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

order by returns query results in the specified columns in sorted order. order by is part of the select command.
In Transact-SQL, you can use order by to sort items that do not appear in the select list. You can sort by a column heading, a column name, an expression, an alias name (if specified in the select list), or a number representing the position of the item in the select list (select_list_number).
If you sort by select_list_number, the columns to which the order by clause refers must be included in the select list, and the select list cannot be * (asterisk).
Use order by to display your query results in a meaningful order. Without an order by clause, you cannot control the order in which the SAP ASE server returns results.

See also sp_configure, sp_helpsort, sp_lock, sp_sysmon in Reference Manual: Procedures.

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.

The behavior of order by when identical table column name and column alias name exist is a vendor-specific extension of the ANSI SQL standard.