Microsoft SQL ServerORDER 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:

IdFNameLName
2JohnJohnson
1JamesSmith
4JohnathonSmith
3MichaelWilliams

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 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.

IdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHireDate
1JamesSmith1234567890NULL1100001-01-2002
2JohnJohnson24681012141140023-03-2005
3MichaelWilliams13579111311260012-05-2009
4JohnathonSmith12121212122150024-07-2016
5SamSaxon13721413122240025-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:

GroupCount
Not Retired6
Retired4
Total10

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

GroupCount
Total10
Retired4
Not Retired6