Blog Post

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

Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data

Rate

Share

Share

Rate