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:

1 2 3 4 5 6 7 8 9

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 KINGDOMUNITED 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.

1 2 3 4 5 6 7 8

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 KINGDOMUNITED STATES