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 sort order chosen during installation. For information about the effects of different sort orders, see Sort Order.


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, you can use a user-specified name 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


See Also

SELECT


(c) 1988-98 Microsoft Corporation. All Rights Reserved.