How to Use ORDER BY Clause in T-SQL

Syntax

SELECT select_list
FROM table_source
[WHERE ...]
[GROUP BY ...]
ORDER BY sort_key [ASC | DESC] [, sort_key [ASC | DESC] ...]
[OFFSET n ROWS [FETCH NEXT m ROWS ONLY]];

ASC is the default. The direction applies to one key only, so ORDER BY Salary, LastName DESC sorts salary ascending and only the last name descending.

Sorting by several columns

Later keys break ties left by earlier keys:

SELECT FirstName, LastName, Salary
FROM dbo.Employees
ORDER BY Salary DESC, LastName;

FirstName  LastName  Salary
---------  --------  --------
Asha       Rao       92000.00
Chloe      Diaz      78000.00
Farid      Khan      78000.00
Elena      Novak     67000.00
Ben        Carter    61000.00
Dev        Mehta     55000.00

Chloe and Farid earn the same, so LastName decides: Diaz before Khan.

What a sort key can be

A sort key can be a column name, a column alias from the SELECT list, an expression, or the position of a column in the SELECT list.

-- alias
SELECT FirstName + N' ' + LastName AS FullName, Salary
FROM dbo.Employees
ORDER BY FullName;

-- expression
SELECT FirstName, HireDate
FROM dbo.Employees
ORDER BY YEAR(HireDate), MONTH(HireDate);

-- a column that is not in the SELECT list
SELECT FirstName, LastName
FROM dbo.Employees
ORDER BY HireDate;

-- position: works, but avoid it
SELECT FirstName, LastName, Salary
FROM dbo.Employees
ORDER BY 3 DESC;

Sorting by position breaks silently when someone reorders or adds columns to the SELECT list. It is fine for a throwaway query and a bad idea in stored code.

An alias works because ORDER BY is evaluated after SELECT. That is also why an alias is not available in WHERE or GROUP BY. One limit: you can sort by an alias on its own, but not use it inside a larger expression. ORDER BY FullName works. ORDER BY UPPER(FullName) fails with "Invalid column name 'FullName'", so repeat the original expression instead.

Where NULLs go

SQL Server treats NULL as lower than every other value. NULLs come first in ascending order and last in descending order:

SELECT FirstName, Department
FROM dbo.Employees
ORDER BY Department;

FirstName  Department
---------  -----------
Dev        NULL
Asha       Engineering
Chloe      Engineering
Farid      Finance
Ben        Sales
Elena      Sales

T-SQL has no NULLS FIRST or NULLS LAST. To push NULLs to the end of an ascending sort, sort on a flag first:

SELECT FirstName, Department
FROM dbo.Employees
ORDER BY CASE WHEN Department IS NULL THEN 1 ELSE 0 END, Department;

Custom sort orders

A CASE expression also handles orders that are neither alphabetical nor numeric:

SELECT FirstName, Department
FROM dbo.Employees
ORDER BY CASE Department
             WHEN N'Sales'       THEN 1
             WHEN N'Engineering' THEN 2
             ELSE 3
         END,
         FirstName;

TOP and paging

TOP picks its rows according to the ORDER BY. Without one, "top" has no meaning and you get whichever rows the engine reads first.

SELECT TOP (2) EmployeeId, FirstName, Salary
FROM dbo.Employees
ORDER BY EmployeeId DESC;

EmployeeId  FirstName  Salary
----------  ---------  --------
6           Farid      78000.00
5           Elena      67000.00

Add WITH TIES to include every row that ties with the last one. SELECT TOP (2) WITH TIES ... ORDER BY Salary DESC returns three rows here, because two people share second place at 78,000.

For paging, use OFFSET and FETCH, which are part of the ORDER BY clause:

SELECT EmployeeId, FirstName, LastName
FROM dbo.Employees
ORDER BY EmployeeId
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;   -- page 2 with a page size of 2

This returns employees 3 and 4.

Make the order deterministic

If the sort keys do not identify a row uniquely, rows that tie can come back in any order, and that order can differ from one execution to the next. With paging this shows up as rows repeated on two pages or missing from both.

ORDER BY HireDate is ambiguous in the sample data because Ben and Elena were hired on the same day. Add a unique column as the final key:

ORDER BY HireDate, EmployeeId

Case, accents and collation

String sorting follows the collation of the column, not byte order. Under a case-insensitive collation, which is the default on most installations, apple and Apple are equal for sorting purposes. To sort with different rules for one query, name a collation:

SELECT LastName
FROM dbo.Employees
ORDER BY LastName COLLATE Latin1_General_100_BIN2;

Numbers stored in character columns sort as text: '10' comes before '9'. Convert them, with TRY_CONVERT(int, col) if the column may contain non-numeric values, or fix the column's data type.

Errors you will meet

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified." (Msg 1033.) A view or subquery is a set, and sets have no order. Move the ORDER BY to the outermost query. SELECT TOP (100) PERCENT ... ORDER BY inside a view makes the error go away but does not sort anything: the optimizer discards it. An ORDER BY inside a subquery only decides which rows TOP or OFFSET keeps.

"ORDER BY items must appear in the select list if SELECT DISTINCT is specified." (Msg 145.) After DISTINCT removes duplicates there is no single source row left to take the sort value from. Add the column to the SELECT list, or use GROUP BY and sort by an aggregate.

"ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator." (Msg 104.) A set operation gets one ORDER BY, at the very end, and it can use only the column names or aliases from the first query:

SELECT FirstName AS Name FROM dbo.Employees WHERE Department = N'Sales'
UNION ALL
SELECT LastName FROM dbo.Employees WHERE Department = N'Finance'
ORDER BY Name;

Columns you cannot sort on. xml, geography, geometry and the deprecated text, ntext and image types cannot be used as sort keys. Convert to a sortable type first, for example CAST(col AS nvarchar(max)).

Sorting a grouped query. In a grouped query you can sort only by the grouping columns or by aggregates, for example ORDER BY COUNT(*) DESC.

Performance

Sorting is one of the more expensive things a query does. The cost grows faster than the row count, the sort needs a memory grant, and when the grant is too small the sort spills to tempdb, which shows as a warning on the Sort operator in the actual execution plan.

You can often remove the sort entirely with an index whose key order matches the ORDER BY:

CREATE INDEX IX_Employees_Department_HireDate
    ON dbo.Employees (Department, HireDate)
    INCLUDE (FirstName, LastName);

SELECT FirstName, LastName, HireDate
FROM dbo.Employees
WHERE Department = N'Sales'
ORDER BY HireDate;

On a table large enough for the index to be worth using, SQL Server reads the index in order and no Sort operator appears in the plan. The index can be read backwards too, so the same index serves ORDER BY HireDate DESC. It cannot serve mixed directions across several keys, such as ORDER BY Department ASC, HireDate DESC, unless you declare the index keys with those same directions.

Other points worth knowing:

• Wrapping the sort column in a function (ORDER BY YEAR(HireDate)) prevents an index on that column from supplying the order.
• OFFSET 100000 ROWS still reads and discards the first 100,000 rows. For deep paging, remember the last key you showed and filter on it: WHERE EmployeeId > @LastSeenId ORDER BY EmployeeId.
• Do not sort in the database when the client re-sorts anyway.

Further reading

• SELECT - ORDER BY clause (Transact-SQL)
• TOP (Transact-SQL)
• Collation and Unicode support

What causes slow queries in SQL Server, and how do I fix them?

A diagnostic catalogue of slow query causes in SQL Server: how to tell running from waiting, the plan or wait signature of each cause, and the fix.

#execution-plans#performance#troubleshooting