Custom sorting in ORDER BY clause (Using Case statement)

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.

Custom Sorting is nothing but it is in use of case statement in Order by clause.

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, I am using AdventureWorksDW2008R2 and DimProduct table

Now based on the popularity we need a product 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.

For example I want the list of product which name Start from ‘Head Tube’ will display at the top and Product Name start from ‘LL Hub’ display in the 2^nd position and remaining product display in stander order.

SELECT EnglishProductName FROM   [AdventureWorksDW2008R2].[dbo].[DimProduct] ORDER BY CASE WHEN EnglishProductName like 'Head Tube%' THEN '1'               WHEN EnglishProductName Like 'LL Hub%' THEN '2'               ELSE EnglishProductName END ASC