CASE IN ORDER BY - CLAUSE

Hi,

How can we will use the CASE efficiently along with the ORDER BY Clause While Querying.
Consider the sample scripts

DECLARE @Employee TABLE(Id int IDENTITY(1,1),Name varchar(50), Address varchar(300),Phone int)

INSERT INTO @Employee
SELECT 'Chirag Darji','Ahmedabad',123456789 UNION ALL
SELECT 'Dipak Patel','USA', 123456789 UNION ALL
SELECT 'Shailesh Patel','USA', 123456789 UNION ALL
SELECT 'Piyush Vadher','Gujarat', 123456789 UNION ALL
SELECT 'Mihir Panchal','Gujarat', 123456789 UNION ALL
SELECT 'Vishal Patel','Ahmedabad', 123456789 UNION ALL
SELECT 'Thiyagarajan','Arcot', 123333389 UNION ALL
SELECT 'SureshBabu','TamilNadu', 12342349

SELECT * FROM @Employee

output:

Here in the above result the records are displayed on which order they inserted. But the user wants the USA Address as the First Rows,TamilNadu Address as the second row and the remaining rows will follows.

To achieve this we can use the CASE in the ORDER BY Clause as below.
Sample Code:
SELECT * FROM @Employee
ORDER BY CASE Address
WHEN 'USA' THEN 1
WHEN 'TamilNadu' THEN 2
ELSE 3
END

Output:

I hope it will help you guys

Regards

Thiyagarajan