SQL Server – Custom sorting in ORDER BY clause
ORDER BY clause can be used to sort the results returned by SELECT statement in SQL Server. It orders the result set by specified column list. When used with character data type columns it sorts data in dictionary-order.
Sometimes, we need result set to be sorted in a custom order, for example, a specific value must appear at top of result set, and others can be sorted in standard order.
for example, consider following list of countries:
CountryName
AUSTRALIA
BANGLADESH
CHINA
FRANCE
INDIA
JAPAN
NEW ZEALAND
PAKISTAN
SRI LANKA
UNITED KINGDOM
UNITED STATES
Now based on the popularity you might need a country to appear on top of the list. In order to return results as required, we need to specify a custom sort order in ORDER BY clause. It can be used as below.
The following query will return result set ordered by CountryName, but INDIA at top and CHINA at 2nd position:
USE [SqlAndMe]
GO
SELECT CountryName
FROM dbo.Country
ORDER BY CASE WHEN CountryName = 'INDIA' THEN '1'
WHEN CountryName = 'CHINA' THEN '2'
ELSE CountryName END ASC
GO
Result Set:
CountryName
INDIA
CHINA
AUSTRALIA
BANGLADESH
FRANCE
JAPAN
NEW ZEALAND
PAKISTAN
SRI LANKA
UNITED KINGDOM
UNITED STATES
As you can see from the results above, both results are now in desired position, while remaining values are sorted in a standard order.
Another variation we can use to place only one row at top of result set is set it’s order to NULL, since NULLs appear first in ordered result set.
USE [SqlAndMe]
GO
SELECT CountryName
FROM dbo.Country
ORDER BY CASE WHEN CountryName = 'INDIA' THEN NULL
ELSE CountryName END ASC
GO
Result Set:
CountryName
INDIA
AUSTRALIA
BANGLADESH
CHINA
FRANCE
JAPAN
NEW ZEALAND
PAKISTAN
SRI LANKA
UNITED KINGDOM
UNITED STATES
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Leave a comment
Contact Me
Sql And Me 
Worth noting if you use DISTINCT in the SELECT the ORDER BY Case will error, you have to split the SELECT DISTINCT into say a CTE and then SELECT FROM the CTE to employ your ORDER BY CASE structure. Still a great idea.
Thanks for this example, it was very useful to me.
I have another more complex use case that I managed to solve that others may also be interested in. I had a list of users that I am filtering based on a search string. The query returns all users where any of their names (first, middle or last) match the search string, for example the search string “ne” would return Ned Flanders and Nelly Furtado but also Isaac Newton and John Ne Doe:
SELECT full_name FROM users WHERE full_name LIKE ‘ne%’ OR full_name LIKE ‘% ne%’
Now we add the requirement that all first name matches appear at the top of the list followed by all the other name matches:
SELECT full_name FROM users WHERE full_name LIKE ‘ne%’ OR full_name LIKE ‘% ne%’ ORDER BY CASE WHEN full_name LIKE ‘ne %’ THEN ‘1’ ELSE full_name END
The only problem with this is that all the first name matches at the top of the list won’t be sorted alphabetically, their sorting appears to be quite arbitrary. The way I solved this was to still use the string literal ‘1’ as a sort key but to also concatenate full_name:
SELECT full_name FROM users WHERE full_name LIKE ‘ne%’ OR full_name LIKE ‘% ne%’ ORDER BY CASE WHEN full_name LIKE ‘ne %’ THEN CONCAT(‘1’, full_name) ELSE full_name END
Hope someone finds this useful!
Absolutely brilliant. Thank you very much Mike, I was trying to solve just that problem
Thanks dear
What if I want to place my specific record at the bottom of the resultant.?
Like: INDIA should be in Bottom of the resultant list
AUSTRALIA
BANGLADESH
CHINA
FRANCE
JAPAN
NEW ZEALAND
PAKISTAN
SRI LANKA
UNITED KINGDOM
UNITED STATES
INDIA