Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

Leave a comment on the original post [sqlandme.com, opens in a new window]

Loading comments...