Returns query results in the specified columns in sorted order.
[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]
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.
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
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
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
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
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.
Adaptive Server interprets a column name in the order by clause as the alias name when these three conditions exist:
The order by clause contains a reference to a qualified column name (that is, order by table.column).
Both the table column name and alias name exist.
The names for both the table column and alias are identical to the column name in the order by clause.
In this example, the result set from the two queries differ, even though the order by clause is identical; yet, the order by clause refers to a different column in both cases.
create table t (A int, B char(3)) insert into t (A, B) values(1, 'az') insert into t (A, B) values(2, 'bb') go /* t.B refers to the table column B */ select A, reverse(B) as C from t order by t.B go /* t.B refers to the alias column B */ select A, reverse(B) as B from t order by t.B go
A C ----------- --- 1 za 2 bb (2 rows affected) A B ----------- --- 2 bb 1 za (2 rows affected)
This behavior occurs because Adaptive Server allows the order by clause to reference an alias column name that is qualified by the table name. When a base table column also exists with the same colum name as the alias column, Adaptive Server gives precedence to the alias column.
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.
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 national languages may also be provided.
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”. |
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. This sort order 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 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.
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 keys. You cannot use the descending scan optimization 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. You can also use descending scan optimization 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 must return results in descending order, but the descending scans optimization creates deadlock problems, some possible workarounds are:
Use set transaction isolation level 0 scans for descending scans. For more information on the effect of isolation level 0 reads, see the set command, and “Using Locking Commands” in Performance and Tuning Guide: Locking.
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. See the System Administration Guide.
Break problematic 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 “Indexes” in Performance and Tuning Guide: Basics.
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.
The behavior of order by when identical table column name and column alias name exist is a vendor-specific extension of the ANSI SQL standard.
Commands compute clause, declare, group by and having clauses, select, where clause
System procedures sp_configure, sp_helpsort, sp_lock, sp_sysmon
