Home

	Expand Search

Reference Manual: Commands

Chapter 1 Commands

order by clause

order by clause

Description

Returns query results in the specified column(s) 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

sorts the results by columns.

sorts the results in ascending order. If you do not specify asc or desc, asc is assumed.

sorts the results in descending order.

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 column(s) 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.

• order by cannot be used on text 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

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

• With order by, null values precede all others.

• The sort order (collating sequence) on your Adaptive Server determines how your data is sorted. The sort order choices are binary, dictionary, case-insensitive, case-insensitive with preference, and case- and accent-insensitive. Sort orders that are specific to specific national languages may also be provided.

  Table 1-31: Effect of sort order choices

  Adaptive Server sort order	Effects on order by results
  Binary order	Sorts all data according to the numeric byte-value of each character in the character set. Binary order sorts all uppercase letters before lowercase letters. Binary sort order is the only option for multibyte character sets.
  Dictionary order	Sorts uppercase letters before their lowercase counterparts (case-sensitive). Dictionary order recognizes the various accented forms of a letter and sorts them after the unaccented form.
  Dictionary order, case-insensitive	Sorts data in dictionary order but does not recognize case differences. Uppercase letters are equivalent to their lowercase counterparts and are sorted as described in "Sort rules" in the following section.
  Dictionary order, case-insensitive with preference	Sorts an uppercase letter in the preferred position, before its lowercase version. It does not recognize case difference when performing comparisons (for example, in where clauses).
  Dictionary order, case- and accent-insensitive	Sorts data in dictionary order, but does not recognize case differences; treats accented forms of a letter as equivalent to the associated unaccented letter. It intermingles accented and unaccented letters in sorting results.

• sp_helpsort reports the sort order installed on Adaptive Server.

• When two rows have equivalent values in Adaptive Server's 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.

• Use of the keyword desc in an order by clause allows the query optimizer to choose a strategy that eliminates the need for a worktable and a sort step to return results in descending order. This optimization scans the page chain of the index in reverse order, following the previous page pointers on each index page.

  To use this optimization, the columns in the order by clause must match the index order. They can be a subset of the keys, but must be a prefix subset, that is, they must include the first key(s). The descending scan optimization cannot be used if the columns named in the order by clause are a superset of the index keys.

  If the query involves a join, all tables can be scanned in descending key order, as long as the requirements for a prefix subset of keys are met. Descending scan optimization can also be used for one or more tables in a join, while other tables are scanned in ascending order.

• If other user processes are scanning forward to perform updates or deletes, performing descending scans can cause deadlocks. Deadlocks may also be encountered during page splits and shrinks. You can use sp_sysmon to track deadlocks on your server, or you can use the configuration parameter print deadlock information to send deadlock information to the error log.

• If your applications need to return results in descending order, but the descending scans optimization creates deadlock problems, some possible workarounds are:

  • Use transaction isolation level 0 scans for descending scans. For more information on the effect of isolation level 0 reads, see the Performance and Tuning Guide.

  • Disable descending scan optimization with the configuration parameter allow backward scans so that all queries that use desc scan the table in ascending order and sort the result set into descending order. For more information, see the System Administration Guide.

  • Break problematical descending scans into two steps, selecting the required rows into a temporary table in ascending order in the first step, and selecting from the temporary table in descending order in the second step.

• If a backward scan uses a clustered index that contains overflow pages because duplicate key values are present, the result set returned by the descending scan may not be in exact reverse order of the result set that is returned with an ascending scan. The specified key values are returned in order, but the order of the rows for the identical keys on the overflow pages may be different. For an explanation of how overflow pages in clustered indexes are stored, see the Performance and Tuning Guide.

Standards

ANSI SQL - Compliance level: Transact-SQL extension.

Specifying new column headings in the order by clause of a select statement when the union operator is used is a Transact-SQL extension.

See also

Commands

compute clause, declare, group by and having clauses, select, where clause

System procedures

sp_configure, sp_helpsort, sp_lock, sp_sysmon