Using CASE with ORDER BY in SQL Server

        Today I came to know that we can use CASE with ORDER BY clause.  Its a nice functionality which helps you to avoid UNIOUN 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.

   1: SET ANSI_NULLS ON

   2: GO

   3: SET QUOTED_IDENTIFIER ON

   4: GO

   5: CREATE TABLE [dbo].[Employee](

   6:     [Id] [int] IDENTITY(1,1) NOT NULL,

   7:     [Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

   8:     [Address] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

   9:     [Phone] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  10:  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 

  11: (

  12:     [Id] ASC

  13: )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

  14: ) ON [PRIMARY]

Fig - (1) Employee  table.

         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')

 Fig - (2)  Insert Statements

     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,

   1: 2    Dipak Patel          USA       123456789

   2: 3    Shailesh Patel       USA       123456789

   3: 6    Vishal Patel         Ahmedabad 123456789

   4: 1    Chirag Darji         Ahmedabad 123456789

   5: 4    Piyush Vadher        Gujarat   123456789

   6: 5    Mihir Panchal        Gujarat   123456789

   7: 7    Mayank Champaneri    Palitana  123456789

   8: 8    Manish Pansinita     Sanand    123456789

   9: 9    Manish Pansinita     Baroda    123456789

Fig - (3)  Result

      Below is the query that displays the result as shown in fig - (3),

   1: SELECT * FROM Employee ORDER BY

   2: CASE WHEN Address = 'USA' THEN 1

   3: WHEN Address = 'Ahmedabad' THEN 2

   4: WHEN Address = 'Gujarat' THEN 3

   5: ELSE 4

   6: END

Happy Programming !!!