How to sort result set in custom order in SQL?

Question:

Write a SQL query to display the result in custom sort order.

Answer:

If you need to display the result in ascending or descending order you can achieve the same directly using asc or desc keyword in order byclause. But if you need to display it in custom order in report, you can use the case expression to do it.

-- solution: custom ordering in sql server
select city_name
from dbo.city
order by case when city_name = ‘Helsinki’ then 10
 when city_name = ‘Copenhagen’ then 20
 when city_name = ‘Stockholm’ then 30
 else 100 end

If you check the case expression result below, I have used the numbers 10, 20 etc. But it does not matter. You can use number or letters in ascending order to display the result in ascending order.

If you like this SQL interview question, you may also like the below scenario based interview question and answers.

How to generate permutations in SQL?
How to do reciprocal in SQL?
How to find account balance (running total) in SQL?

Originally published at blog.rajanand.org.