Transact-SQL User's Guide

Home

Selecting groups
of data: the having clause

Summarizing
groups of data: the compute clause

Transact-SQL User's Guide

Chapter 3 Using Aggregates, Grouping, and Sorting

Sorting query results: the order by clause

The order by clause allows you to sort query results by one or more columns, up to 31. Each sort is either ascending (asc) or descending (desc). If neither is specified, asc is the default. The following query orders results 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    UNDECIDED        MC3026 
0877    mod_cook         MC2222 
0877    mod_cook         MC3021 
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 rows affected)

If you name more than one column in the order by clause, Adaptive Server nests the sorts. The following statement sorts the rows in the stores table first by stor_id in descending order, then by payterms (in ascending order, since desc is not specified), and finally by country (also ascending). Adaptive Server sorts null values first within any group.

select stor_id, payterms, country
from stores
order by stor_id desc, payterms

stor_id payterms     country 
------- ------------ ------------ 
8042    Net 30       USA          
7896    Net 60       USA          
7131    Net 60       USA          
7067    Net 30       USA          
7066    Net 30       USA          
6380    Net 60       USA          
5023    Net 60       USA          

(7 rows affected)

You can use the position number of a column in a select list instead of the column name. Column names and select list numbers can be mixed. 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

Most versions of SQL require that order by items appear in the select list, but Transact-SQL has no such restriction. You could order the results of the preceding query by title, although that column does not appear in the select list.

You cannot use order by on text or image columns.

Adaptive Server does not allow subqueries, aggregates, variables and constant expressions in the order by list.

With order by, null values come before all others.

The effects of an order by clause on mixed-case data depend on the sort order installed on your Adaptive Server. The basic choices are binary, dictionary order, and case-insensitive. The system procedure sp_helpsort displays the sort order for your server. See the Reference Manual for more information on sort orders.

order by and group by

You can use an order by clause to order the results of a group by in a particular way.

Put the order by clause after the group by clause. For example, to find the average price of each type of book and order the results by average price, the statement is:

select type, avg(price) 
from titles 
group by type 
order by avg(price)

type 
---------- ------------ 
UNDECIDED           NULL 
mod_cook          11.49 
psychology        13.50 
business          13.73 
trad_cook         15.96 
popular_comp      21.48 
 
(6 rows affected)

order by and group by used with select distinct

A select distinct query with order by or group by can return duplicate values if the order by or group by column is not in the select list. For example:

select distinct pub_id
from titles
order by type

pub_id
------
0877
0736
1389
0877
1389
0736
0877
0877
 
(8 rows affected)

If a query has an order by or group by clause that includes columns not in the select list, Adaptive Server adds those columns as hidden columns in the columns being processed. The columns listed in the order by or group by clause are included in the test for distinct rows. To comply with ANSI standards, include the order by or group by column in the select list. For example:

select distinct pub_id, type
from titles
order by type

pub_id type 
------ ------------
0877   UNDECIDED 
0736   business 
1389   business 
0877   mod_cook 
1389   popular_comp
0736   psychology
0877   psychology
0877   trad_cook
 
(8 rows affected)


	Expand Search