How to do custom sorting in SQL Server?

 Posted by Bandi on 9/10/2013 | Category: Sql Server Interview questions | Views: 1540 | Points: 40

By using CASE statement in the ORDER BY clause we can achieve Custom Sorting on result set....
For example,
If you want to give the 1st preference to Mango, 2nd for banana, 3rd for Apple and so on
CREATE TABLE UserPreferences(FruitId int identity, FruitName varchar(40))

INSERT INTO UserPreferences VALUES( 'Apple'), ('Cherry'), ('Mango'), ('Banana')
SELECT * FROM UserPreferences
/*Sample Data:
FruitId FruitName
1 Apple
2 Cherry
3 Mango
4 Banana
Answer should be:
FROM UserPreferences
ORDER BY CASE WHEN FruitName = 'Mango' THEN 1
WHEN FruitName = 'Banana' THEN 2
WHEN FruitName = 'Apple' THEN 3
/*RESULT in custome order:
FruitId FruitName
3 Mango
4 Banana
1 Apple
2 Cherry*/

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response