ORDER BY Clause (Transact-SQL)
Specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
 Note: |
|---|
| When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself. |
Transact-SQL Syntax Conventions
- order_by_expression
-
Specifies a column on which to sort. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list. An integer cannot be specified when the order_by_expression appears in a ranking function. A sort column can include an expression, but when the database is in SQL Server 2005 (90) compatibility mode the expression cannot resolve to a constant. Column names and aliases can be qualified by the table or view name. In SQL Server 2005, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement.
Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.
The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
Additionally, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.
Note: ntext, text, image, or xml columns cannot be used in an ORDER BY clause.
- COLLATE {collation_name}
-
Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name, and not according to the collation of the column as defined in the table or view. collation_name can be either a Windows collation name or a SQL collation name. For more information, see Collation Settings in Setup and Using SQL Collations. COLLATE is applicable only for columns of the char, varchar, nchar, and nvarchar data types.
- ASC
-
Specifies that the values in the specified column should be sorted in ascending order, from lowest value to highest value.
- DESC
-
Specifies that the values in the specified column should be sorted in descending order, from highest value to lowest value.
Null values are treated as the lowest possible values.
There is no limit to the number of items in the ORDER BY clause. However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.
When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.
Reference
Expressions (Transact-SQL)SELECT (Transact-SQL)
FROM (Transact-SQL)
Other Resources
Sorting Rows with ORDER BYHelp and Information
Getting SQL Server 2005 AssistanceSELECT '0' as SYSTEMERROR FROM REQUS WHERE REQUEST IN ('G', 'V') UNION ALL SELECT SYSTEMERROR FROM STATS WHERE STATSTYPE = '1' AND REQUEST IN ('G', 'V') order by DOCNO DESC, CAST(SYSTEMERROR AS DECIMAL) DESC
In this query, SYSTEMERROR is not a column in this table.
the following sql statement gives error
Msg 207, Level 16, State 1, Line 2
Invalid column name 'SYSTEMERROR'.
Msg 104, Level 16, State 1, Line 2
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator."
- 6/11/2008
- tyredof
Thought an example might come in handy:
ORDERTABLE:
Company Order
Microsoft 1234
A Company 2345
B Company 3456
Microsoft 0123
Example: To display the company names in alphabetical order:
SELECT Company, Order FROM Ordertable
ORDER BY Company
Result:
Company Order
A Company 2345
B Company 3456
Microsoft 1234
Microsoft 0123
Example: To display the company names in alphabetical order AND orders in numerical order:
SELECT Company, Order FROM Ordertable
ORDER BY Company, Order
Result:
Company Order
A Company 2345
B Company 3456
Microsoft 0123
Microsoft 1234
Example: To display the company names in REVERSE alphabetical order AND the orders in numerical order:
SELECT Company, Order FROM Ordertable
ORDER BY Company DESC, Order ASC
Result:
Company Order
Microsoft 0123
Microsoft 1234
B Company 3456
A Company 2345
- 11/27/2007
- NicolaBossuyt
