Microsoft SQL Server

ORDER BY

Remarks#

The purpose of the ORDER BY clause is to sort the data returned by a query.

It’s important to note that the order of rows returned by a query is undefined unless there is an ORDER BY clause.

See MSDN documentation for full details of the ORDER BY clause: https://msdn.microsoft.com/en-us/library/ms188385.aspx

Simple ORDER BY clause

Using the Employees Table, below is an example to return the Id, FName and LName columns in (ascending) LName order:

SELECT Id, FName, LName FROM Employees
ORDER BY LName

Returns:

Id FName LName
2 John Johnson
1 James Smith
4 Johnathon Smith
3 Michael Williams

To sort in descending order add the DESC keyword after the field parameter, e.g. the same query in LName descending order is:

SELECT Id, FName, LName FROM Employees
ORDER BY LName DESC

ORDER BY multiple fields

Multiple fields can be specified for the ORDER BY clause, in either ASCending or DESCending order.

For example, using the https://stackoverflow.com/documentation/sql/280/example-databases/1207/item-sales-table#t=201607211314066434211 table, we can return a query that sorts by SaleDate in ascending order, and Quantity in descending order.

SELECT ItemId, SaleDate, Quantity
FROM [Item Sales]
ORDER BY SaleDate ASC, Quantity DESC

Note that the ASC keyword is optional, and results are sorted in ascending order of a given field by default.

ORDER BY with complex logic

If we want to order the data differently for per group, we can add a CASE syntax to the ORDER BY. In this example, we want to order employees from Department 1 by last name and employees from Department 2 by salary.

Id FName LName PhoneNumber ManagerId DepartmentId Salary HireDate
1 James Smith 1234567890 NULL 1 1000 01-01-2002
2 John Johnson 2468101214 1 1 400 23-03-2005
3 Michael Williams 1357911131 1 2 600 12-05-2009
4 Johnathon Smith 1212121212 2 1 500 24-07-2016
5 Sam Saxon 1372141312 2 2 400 25-03-2015
The following query will provide the required results:
SELECT Id, FName, LName, Salary FROM Employees
ORDER BY Case When DepartmentId = 1 then LName else Salary end

Custom Ordering

If you want to order by a column using something other than alphabetical/numeric ordering, you can use case to specify the order you want.

order by Group returns:

Group Count
Not Retired 6
Retired 4
Total 10

order by case group when 'Total' then 1 when 'Retired' then 2 else 3 end returns:

Group Count
Total 10
Retired 4
Not Retired 6

This modified text is an extract of the original Stack Overflow Documentation created by the contributors and released under CC BY-SA 3.0 This website is not affiliated with Stack Overflow