order by clause

Description

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

Examples

Example 1

Selects the titles whose price 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 Adaptive Server returns results.

• The maximum number of columns allowed in an order by clause is 31.

• You cannot use order by on text, unitext, or image datatype columns.

• Subqueries and view definitions cannot include an order by clause (or a compute clause or the keyword into). Conversely, you cannot use a subquery in an order by list.

• You cannot update the result set of a server- or language- type cursor if it contains an order by clause in its select statement. For more information about the restrictions applied to updatable cursors, see the Transact-SQL User’s Guide.

• If you use compute by, you must also use an order by clause. The expressions listed after compute by must be identical to or a subset of those listed after order by, must be in the same left-to-right order, must start with the same expression, and must not skip any expressions. For example, if the order by clause is:

  order by a, b, c
  

  the compute by clause can be any (or all) of these:

  compute by a, b, c 
  compute by a, b 
  compute by a
  

  You can also use the keyword compute can be used without by to generate grand totals, grand counts, and so on. In this case, order by is optional.

• When two rows have equivalent values in the Adaptive Server sort order, the following rules are used to order the rows:

  • The values in the columns named in the order by clause are compared.

  • If two rows have equivalent column values, the binary value of the entire rows is compared byte by byte. This comparison is performed on the row in the order in which the columns are stored internally, not the order of the columns as they are named in the query or in the original create table clause. In brief, data is stored with all the fixed-length columns, in order, followed by all the variable-length columns, in order.

  • If rows are equal, row IDs are compared.

    Given this table:

    create table sortdemo (lname varchar (20),
                           init char (1) not null)
    

    and this data:

    lname      init 
    ---------- ---- 
    Smith      B 
    SMITH      C
    smith      A 
    

    you get these results when you order by lname:

    lname      init 
    ---------- ---- 
    smith      A 
    Smith      B 
    SMITH      C
    

    Since the fixed-length char data (the init column) is stored first internally, the order by sorts these rows based on the binary values “Asmith”, “BSmith,” and “CSMITH”.

    However, if the init is of type varchar, the lname column is stored first, and then the init column. The comparison takes place on the binary values “SMITHC”, “SmithB”, and “smithA”, and the rows are returned in that order.