A CASE statement is an expression to evaluate different conditions and return a scalar value when a condition is met. If none of the condition evaluated to TRUE it will return a value from ELSE block which is optional. ORDER BY clause used to sort the values in either ascending or descending order.
A practical situation arises when we need to use a CASE statement in ORDER BY clause to alter the order and instruct the query to Sort the output based on certain requirement. For an example, we might need to place NULL values at the end of query result set. We need to display a particular country name at the top even if doesn’t begin with character A. We need to display company’s CEO name first while sorting the employee names thereafter.
Let’s see a few examples of how CASE statement can be combined with ORDER BY clause to tweak the sort order as per our requirement.
Sort NULLs Last – CASE Statement In ORDER BY
The default behaviour of ORDER BY clause with NULLs is sorting them first. A column having NULLs, if sorted will display NULLs first then the sorted values. If we have a requirement to place NULLs at the end query result set then we can use CASE statement with ORDER BY clause.
In the following example, we’re using CASE statement to check if the row value is NULL then assign a number as 1 and for all the NOT NULL values assign a number 0. We’re creating a virtual derived column in ORDER BY clause and this will help us to order the rows having value as 0 on the top followed by NULLs as 1 when sorted in ascending order.
Sort Particular String First – CASE Statement In ORDER BY
Let’s consider we’re extracting a report having employee names and requested to CEO’s name on the top followed by other important designations. To achieve the desired result, we would need to tweak the ORDER BY clause by adding a CASE statement and defining the value which has to be sorted first and followed by the rest of values. We’re assigning values in increasing order starting with 0 and creating a virtual derived column to achieve this sort order.
Sort Gender – CASE Statement In ORDER BY
We have given a query and we need to sort Male players above Female. If we run a query with default ORDER BY clause then it will logically sort Female before Male considering alphabetical order of character F before M.
Run the following example where we’ve included CASE statement in ORDER BY clause to tweak the order of Gender column by following the same technique used in the above examples.
Sort Using Specific Columns – CASE Statement In ORDER BY
What if we’ve been asked to sort by two different columns based on a certain condition? It’s achievable by using CASE statement with ORDER BY clause. In the following example, we’re sorting based on a condition where Gender is Male then sort the result using Country column else sort it using Name column.
These examples demonstrate it’s not just using numbers with a case statement. However, we can use different table columns while using CASE statement with ORDER BY in SQL server.
In this tech-recipes post we have learnt how to use ORDER BY Clause in CASE Statement to achieve SORT order as per our requirement. If you like this post you may browse to Tech-Recipes Database Archive posts.
Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.