The ORDER BY clause sorts query results by one or more columns (up to 16 columns). A sort can be ascending (ASC) or descending (DESC). If neither is specified, ASC is assumed.
Important The exact results of an ORDER BY clause depend on the sort order chosen during installation. For information about the effects of different sort orders, see Microsoft SQL Server Setup.
The following query returns results ordered 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 |
mod_cook |
MC2222 |
0877 |
mod_cook |
MC3021 |
0877 |
psychology |
PS1372 |
0877 |
trad_cook |
TC3218 |
0877 |
trad_cook |
TC4203 |
0877 |
trad_cook |
TC7777 |
0877 |
UNDECIDED |
MC3026 |
1389 |
business |
BU1032 |
1389 |
business |
BU1111 |
1389 |
business |
BU7832 |
1389 |
popular_comp |
PC1035 |
1389 |
popular_comp |
PC8888 |
1389 |
popular_comp |
PC9999 |
(18 row(s) affected)
If more than one column is named in the ORDER BY clause, sorts are nested. The following statement sorts the rows in the titles table, first by publisher in descending order, and then by type in ascending order within each publisher, and finally by price (also ascending, because DESC is not specified). Null values are sorted first within any group:
SELECT pub_id, type, title_id, price FROM titles ORDER BY pub_id desc, type, price
The number of the position of a column in a select list can be used 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
Note You cannot use ORDER BY on columns that have the text or image datatypes. Also, subqueries, aggregates, and constant expressions are not allowed in the ORDER BY list; however, you can use the ordinal position in the select list (for example, ORDER BY 2) for aggregates or expressions. For example:
SELECT type, sum (ytd_sales) FROM titles GROUP BY type ORDER BY 2