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:

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.

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:

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