Home » How to Implement Custom Sorting in SQL ?

How to Implement Custom Sorting in SQL ?

 How to implement custom sorting using SQL ORDER BY CLAUSE ?




Sometimes we have a requirement to sort the result set returned by select statement in a custom order , like to display any particular column values at top of result set, while remaining can be in standard sorting order.

It becomes challenge as we know that the Order by clause sort the result set either ascending or descending order based on given columns lists in Select Statement and it does not allow to specify the sorting order for any specific values on column.

Lets try to understand it with an example, here have a sample table – Region as given below.

CREATE TABLE Region (RegionId INT, Region VARCHAR(10))

INSERT INTO Region (RegionID, RegionName)
VALUES (1 , 'EASTERN'), 
       (2, 'WESTERN'), 
       (3, 'NORTHEN'), 
       (4, 'SOUTHERN')

 

Now our requirement is to display the RegionName –‘NORTHEN’ always at on the top of sorting result set regardless whatever the sorting order is used in select statement .

Before implementing the custom sorting query for this, Lets check can we achieve this just by sorting a query result either in ascending or descending order based on RegionName.

SELECT * FROM Region ORDER BY RegionName ASC

As you can see, still Eastern is on the top in result set that means we can get ‘Northen’ on the top just by applying sorting order as ascending on RegionName.

Now Lets check by sorting the result based on RegionName in descending order.

SELECT * FROM Region ORDER BY RegionName ASC

You can see, sorting the result based on RegionName in descending order does not work either, that means to achieve this you need to implement custom sorting.

Lets implement the custom sorting in order to display ‘Northen’ always on the top, and remaining RegionName will be sorted in ascending order.

To implement this you need to use order by clause with SQL case statement as given below.

SELECT * FROM Region ORDER BY CASE
WHEN RegionName ='NORTHEN' THEN '1' ELSE RegionName END

 

 

As you can see, Now it returns the ‘Northern’ at on the top of result set that is what you want to see in result set and other values follows the standard sorting order that is in ascending order.

What happens when you use the sorting order as descending in select statement ?

As we have already seen, sorting the result in descending order we get ‘Western’ on the top of result set.

So to get ‘Northern’ on the top in result set while other RegionName are sorted in descending order, we will implement the custom sorting as followng.

 

 

SELECT * FROM Region ORDER BY CASE 
WHEN RegionName ='NORTHEN' THEN 'zzzzzzzz' 
ELSE RegionName END DESC

Now you can see, Northen is on the top of result set, while other regionname are sorted in descending oreder.

Lets understand above statement, The case statement sepecified in ORDER BY caluse replace the RegionName value ‘Northern’ with ‘zzzzzzzz’, as this time you want to display result set in descending order but also want to display Northern’ at on the top so replaced the ‘Northen’ with character  ‘zzzzzzzz’. As character ‘Z’ is always the last word in dictionary, and order by clause sorts data in dictionary order therefore statement returns the ‘Northern’ always first becuase we have replaced ‘Northen’ value to ‘zzzzzzzz’ in Case Statement, while remaining values are sorted as per standard sorting order.

Here you can also specify the single character ‘Z’ only when your are sure on that all values for RegionName column does not have values starting with letter ‘Z’.

If so, then to avoid any mismatch in sorting order, you can wisely specify any replacement characters for your value that you want keep always on the top in sorting order.




 

Recommended for you

SQL Server Case Statement

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.