How to implement custom sorting using SQL ORDER BY CLAUSE ?
Assume that, you have 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 records follow the 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 provided columns lists in select statement and it does not allow to specify the sorting order for any particular values in column.
Let’s try to understand it with the help of example, here have a sample table named 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, the requirement is to display the RegionName ‘NORTHEN’ should be displayed at the top after sorting the 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.
Let’s sort the records in table by RegionName in ascending order.
SELECT * FROM Region ORDER BY RegionName ASC
As you can see, Eastern is on the top in result set that means we can not 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 DESC
You can see, now WESTERN is at the top. So, 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 we 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 following.
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 specified in ORDER BY clause replaced the RegionName value ‘NORTHEN ‘ with ‘zzzzzzzz’, as this time we want to display the result set in descending order but also want to keep ‘NORTHEN’ at on the top so we replaced the ‘NORTHEN ‘ with character ‘zzzzzzzz’.
As character ‘Z’ is always the last word in alphabetical order, and order by clause sorts data in alphabetical order therefore the statement returns the ‘NORTHEN ‘ always first because 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 you are sure that all values for RegionName column does not have values starting with letter ‘Z’.
If so, then to avoid incorrect sorting order, you can specify any replacement characters for your value that you want keep always on the top in sorting order.
Also Read..
