Using CASE with ORDER BY in SQL Server

Using CASE with ORDER BY in SQL Server

 Use CASE with ORDER BY clause.  Its a nice functionality which helps you to avoid UNION when you have to display the records in some specific order and still you need to sort them.

As shown below I have created an Employee table with ID (Primary key),Name, Address and Phone columns.

I have also inserted some temporary data as shown below,

1: INSERT INTO Employee VALUES (‘Chirag Darji’,‘Ahmedabad’,‘123456789′)

2: INSERT INTO Employee VALUES (‘Dipak Patel’,‘USA’,‘123456789′)

3: INSERT INTO Employee VALUES (‘Shailesh Patel’,‘USA’,‘123456789′)

4: INSERT INTO Employee VALUES (‘Piyush Vadher’,‘Gujarat’,‘123456789′)

5: INSERT INTO Employee VALUES (‘Mihir Panchal’,‘Gujarat’,‘123456789′)

6: INSERT INTO Employee VALUES (‘Vishal Patel’,‘Ahmedabad’,‘123456789′)

Now consider that have to display the records of employee table order by Address however you want first it should display all the records with USA in address field then Ahmedabad then Gujarat and after that all. Here is the output that we want,

Below is the query that displays the result as shown in above fig,

SELECT * FROM Employee ORDER BY

CASE WHEN Address = ‘USA’ THEN 1

WHEN Address = ‘Ahmedabad’ THEN 2

WHEN Address = ‘Gujarat’ THEN 3

ELSE 4

END