Answer: 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))
GO
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:
SELECT *
FROM UserPreferences
ORDER BY CASE WHEN FruitName = 'Mango' THEN 1
WHEN FruitName = 'Banana' THEN 2
WHEN FruitName = 'Apple' THEN 3
ELSE 4
END
/*RESULT in custome order:
FruitId FruitName
3 Mango
4 Banana
1 Apple
2 Cherry*/
Asked In: Many Interviews |
Alert Moderator