Sorting Rows with ORDER BY

The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. For more information about the maximum ORDER BY clause size, see SELECT.

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 collation chosen during installation. For information about the effects of different collations, see SQL Server Collation Fundamentals.

This query returns results ordered by ascending pub_id:

USE pubs
SELECT pub_id, type, title_id
FROM titles
ORDER BY pub_id

Here is the result set:

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   UNDECIDED    MC3026   
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 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).

USE pubs
SELECT pub_id, type, title_id, price
FROM titles
ORDER BY pub_id DESC, type, price

Note  You cannot use ORDER BY on columns that have the text or image data types. Also, subqueries, aggregates, and constant expressions are not allowed in the ORDER BY list; however,  a user-specified name can be used in the select list for aggregates or expressions, for example:

SELECT type, sum (ytd_sales) AS sales_total
FROM titles
GROUP BY type
ORDER BY sales_total