# ORDER BY

# Simple ORDER BY clause

Using the Employees Table (opens new window), 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 http://stackoverflow.com/documentation/sql/280/example-databases/1207/item-sales-table#t=201607211314066434211 (opens new window) 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

# 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 (opens new window)