Introduction
SQL Server ORDER BY Clause in SQL Server sorts the rows returned by a query. You can arrange results in ascending or descending order, sort by more than one column, and use expressions to control the order. A table does not have a guaranteed row order, so use ORDER BY whenever the sequence of returned rows matters.
SQL Server ORDER BY Syntax
The ORDER BY clause is written after the FROM, WHERE, GROUP BY, and HAVING clauses. It is normally the final clause in a SELECT query, except when a query uses additional options such as OFFSET and FETCH.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC];
ASCsorts values in ascending order. It is the default direction, so it can be omitted.DESCsorts values in descending order.- You can specify more than one sort expression, separated by commas.
Sort Rows in Ascending Order
Ascending order places numeric values from lowest to highest and text values in the order defined by the column’s collation. For dates, ascending order places earlier dates before later dates.
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice ASC;
The query returns products with the lowest unit price first. Because ascending order is the default, the following query produces the same sort direction:
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice;
Sort Rows in Descending Order
Use DESC when you want the highest value or most recent date to appear first. This is common when displaying expensive products, newest orders, or employees with the highest salaries.
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice DESC;
This query sorts products from the highest unit price to the lowest.
Sort by Multiple Columns
You can add multiple columns to ORDER BY to create a primary sort and one or more secondary sorts. SQL Server applies the first expression first. When rows have equal values for that expression, it uses the next expression to order those tied rows.
SELECT FirstName, LastName, DepartmentID, HireDate
FROM dbo.Employees
ORDER BY DepartmentID ASC, HireDate DESC;
In this example, employees are grouped by department number in ascending order. Within each department, the most recently hired employees appear first.
Each sort expression can have its own direction. You do not need to use the same direction for every column.
Sort Using a Column Alias
When a SELECT expression has an alias, SQL Server allows that alias to be referenced in the ORDER BY clause. This is useful when sorting by a calculated value or a renamed output column.
SELECT ProductName,
UnitPrice * UnitsInStock AS InventoryValue
FROM dbo.Products
ORDER BY InventoryValue DESC;
The products are sorted by the calculated inventory value, from highest to lowest. Using a meaningful alias can make the query easier to read than repeating a long expression.
Sort Using an Expression
An ORDER BY expression does not have to be a physical table column. You can sort by arithmetic expressions, string functions, date expressions, and other valid expressions.
SELECT FirstName, LastName, Salary
FROM dbo.Employees
ORDER BY Salary * 12 DESC;
This example sorts employees by their calculated annual salary, assuming the stored salary is monthly. You can also sort by a string expression:
SELECT FirstName, LastName
FROM dbo.Employees
ORDER BY LastName + ', ' + FirstName ASC;
Custom Sorting with CASE
Sometimes alphabetical or numeric sorting is not the order required by an application. A CASE expression lets you define a custom priority for values.
SELECT OrderID, OrderStatus, OrderDate
FROM dbo.Orders
ORDER BY
CASE OrderStatus
WHEN 'Pending' THEN 1
WHEN 'Processing' THEN 2
WHEN 'Shipped' THEN 3
WHEN 'Delivered' THEN 4
ELSE 5
END,
OrderDate DESC;
The query places pending orders first, followed by processing, shipped, and delivered orders. Rows with the same status are then sorted by order date, newest first.
How SQL Server Sorts NULL Values
SQL Server treats NULL as the lowest value for sorting purposes. Therefore, NULL values appear first in an ascending sort and last in a descending sort. If you need a different placement, use a CASE expression to assign NULL rows an explicit sort priority.
SELECT EmployeeID, FirstName, Commission
FROM dbo.Employees
ORDER BY
CASE WHEN Commission IS NULL THEN 1 ELSE 0 END,
Commission ASC;
This expression places employees with a known commission before employees whose commission is NULL, then sorts the known commission values from lowest to highest.
Sort by Text and Collation
Text sorting follows the collation rules of the expression or column. A collation determines details such as whether sorting is case-sensitive, accent-sensitive, and how characters from different languages are compared. Two databases can therefore display text in different orders if their collations differ.
You can specify a collation for a sort expression when a particular comparison rule is required:
SELECT CustomerName
FROM dbo.Customers
ORDER BY CustomerName COLLATE Latin1_General_100_CI_AI ASC;
Choose a collation that is supported by your SQL Server installation and appropriate for the language and comparison behavior your application needs.
Use ORDER BY with TOP
When you combine TOP with ORDER BY, SQL Server can return the first N rows according to the requested sort order. Without an ORDER BY clause, the rows selected by TOP are not guaranteed to be the highest, lowest, newest, or oldest records.
SELECT TOP (5) ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice DESC;
This query returns the five most expensive products. To include every row tied with the last selected sort value, use WITH TIES:
SELECT TOP (5) WITH TIES ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice DESC;
The result may contain more than five rows when additional products share the fifth product’s unit price.
Use ORDER BY for Pagination with OFFSET and FETCH
SQL Server supports OFFSET and FETCH NEXT for returning a page of rows. An ORDER BY clause is required for this syntax.
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY ProductID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
This query skips the first ten rows and returns the next ten. For reliable pagination, use a sort key that produces a predictable order. If the chosen column contains duplicate values, add a unique column as a final tie-breaker, such as the table’s primary key.
ORDER BY with DISTINCT
When DISTINCT is used, SQL Server removes duplicate rows from the selected result. The expressions used for ordering must be compatible with the distinct select list; SQL Server does not allow arbitrary non-selected expressions in every DISTINCT query.
SELECT DISTINCT DepartmentID
FROM dbo.Employees
ORDER BY DepartmentID;
This query returns each department ID once and sorts the resulting values in ascending order.
ORDER BY with GROUP BY
After grouping rows with GROUP BY, you can sort the grouped result by a grouping column or an aggregate expression. This is useful for ranking departments, product categories, or customers by totals.
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM dbo.Employees
GROUP BY DepartmentID
ORDER BY EmployeeCount DESC;
The department with the largest employee count appears first. You can also sort directly by the aggregate expression, although using its alias often makes the query easier to understand.
ORDER BY with UNION
When combining query results with UNION or UNION ALL, place the final ORDER BY after the last SELECT statement. It sorts the combined result set, not each individual SELECT result.
SELECT FirstName AS PersonName
FROM dbo.Employees
UNION
SELECT ContactName
FROM dbo.Customers
ORDER BY PersonName ASC;
The output from both queries is combined and then sorted by the output column name.
ORDER BY in Views, Subqueries, and CTEs
A view, derived table, or common table expression represents a relational result and does not guarantee a stored row order. In general, put ORDER BY in the outermost query that returns rows to the caller. SQL Server permits ORDER BY in certain inner query contexts when it is used with clauses such as TOP, OFFSET, or FOR XML, but that does not guarantee the outer query’s final order.
WITH RecentOrders AS
(
SELECT TOP (20) OrderID, CustomerID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC
)
SELECT OrderID, CustomerID, OrderDate
FROM RecentOrders
ORDER BY OrderDate DESC, OrderID DESC;
The inner ordering determines which twenty rows are selected. The outer ordering explicitly determines how those rows are presented to the caller.
ORDER BY and Window Function Ordering
An ORDER BY inside the OVER() clause controls the logical order used by a window function. It does not automatically sort the final result set. Use a separate query-level ORDER BY when the returned rows themselves must appear in a particular order.
SELECT EmployeeID, DepartmentID, Salary,
ROW_NUMBER() OVER
(
PARTITION BY DepartmentID
ORDER BY Salary DESC, EmployeeID
) AS SalaryRank
FROM dbo.Employees
ORDER BY DepartmentID, SalaryRank;
The window ordering assigns a row number within each department. The final ORDER BY arranges the output rows by department and calculated rank.