Sorting Rows with ORDER BY
The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. For more information about the maximum ORDER BY clause size, see ORDER BY Clause (Transact-SQL).
Microsoft SQL Server 2005 allows specifying ordering columns from tables in the FROM clause that are not specified in the SELECT list. The column names referenced in the ORDER BY clause must correspond to either a column in the SELECT list or a column of the table in the FROM clause without any ambiguities. If column names are aliased in the SELECT list, only the alias name can be used in the ORDER BY clause. Similarly, if table names are aliased in the FROM clause, only the alias names can be used to qualify their columns in the ORDER BY clause.
A sort can be ascending (ASC) or descending (DESC). If neither is specified, ASC is assumed.
The following query returns results ordered by ascending ProductID:
USE AdventureWorks;
GO
SELECT ProductID, ProductLine, ProductModelID
FROM Production.Product
ORDER BY ProductID
If more than one column is named in the ORDER BY clause, sorts are nested. The following statement sorts the rows in the Production.Product table, first by product subcategory in descending order, and then by ListPrice in ascending order within each product subcategory.
USE AdventureWorks;
GO
SELECT ProductID, ProductSubcategoryID, ListPrice
FROM Production.Product
ORDER BY ProductSubcategoryID DESC, ListPrice
The exact results of an ORDER BY clause depend on the collation of the columns being ordered. For more information, see Working with Collations. For char, varchar, nchar, and nvarchar columns, you can specify that an ORDER BY operation be performed according to a collation that is different from the collation of the column as defined in the table or view. You can specify a Windows collation name or a SQL collation name. For example, the LastName column of the Person.Contact table in the AdventureWorks database is defined with the Latin1_General collation, but in the script below, the column is returned in ascending order using the Traditional_Spanish collation.
USE AdventureWorks;
GO
SELECT LastName FROM Person.Contact
ORDER BY LastName
COLLATE Traditional_Spanish_ci_ai ASC
GO
You cannot use ORDER BY on columns that have the text, ntext, image, or xml data types. Also, subqueries, aggregates, and constant expressions are not allowed in the ORDER BY list. However, a user-specified name can be used in the select list for aggregates or expressions. For example:
SELECT Color, AVG (ListPrice) AS 'average list price'
FROM Production.Product
GROUP BY Color
ORDER BY 'average list price'
ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query. For example, consider the following view definition:
CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName
Then query the view:
SELECT * FROM TopView
Although the view definition contains an ORDER BY clause, that ORDER BY clause is used only to determine the rows returned by the TOP clause. When querying the view itself, SQL Server does not guarantee the results will be ordered, unless you specify so explicitly, as shown in the following query:
SELECT * FROM TopView
ORDER BY LastName