ORDER BY with CASE

Most of us must have experienced this problem at least once when you want to order some of your records in a specific way. Or your boss or client asks this weird request to see some specific records on top. And you are puzzelled, how to do this.

The following example shows how we can get this:

01	USE [AdventureWorks]

02

GO

03

04	--Simple sort by ORDER BY

05	SELECT FirstName, LastName

06	FROM Person.Contact

07	ORDER BY FirstName

08

09	-- ORDER BY with CASE

10	SELECT FirstName, LastName

11	FROM Person.Contact

12	ORDER BY CASE LastName

13	WHEN 'Wright'  THEN '0'

14	WHEN 'Jenkins' THEN '1'

15	WHEN 'Torres'  THEN '2'

16	WHEN 'Sanchez' THEN '3'

17	ELSE LastName

18

END

19

20	-- Multiple column sort with ORDER BY with CASE

21	SELECT FirstName, LastName

22	FROM Person.Contact

23	ORDER BY CASE LastName

24	WHEN 'Wright'  THEN '0'

25	WHEN 'Jenkins' THEN '1'

26	WHEN 'Torres'  THEN '2'

27	WHEN 'Sanchez' THEN '3'

28	ELSE LastName

29	END, FirstName