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).
Starting with SQL Server 2005, SQL Server 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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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.Person table in the AdventureWorks2008R2 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 AdventureWorks2008R2;
GO
SELECT LastName FROM Person.Person
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.
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.Person
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;