Microsoft SQL Server ORDER BY with complex logic


Example

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