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
Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data 
