How To Use Case Statement In Order By Clause

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.

1.

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 NULLs Last - Case Statement In Order By Clause
USE tempdb;
GO

IF OBJECT_ID('tempdb..#Fruit') IS NOT NULL
DROP TABLE #Fruit;
GO

CREATE TABLE #Fruit
(
 Name VARCHAR(50)
);

INSERT INTO #Fruit VALUES 
 ('Mango') 
,('Apple') 
,('Banana')
,(NULL)
,('Tomato');

--By default ORDER BY Sorts NULL First
SELECT   Name 
FROM     #Fruit 
ORDER BY Name;

--We can Force ORDER BY to Sort NULLs Last
SELECT   Name 
FROM     #Fruit 
ORDER BY CASE WHEN Name IS NULL THEN 1 ELSE 0 END, Name;

2.

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 Specific Name First - Case Statement In Order By Clause

USE tempdb;
GO

IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
DROP TABLE #Employee;
GO

CREATE TABLE #Employee
(
  Name VARCHAR(50)
 ,Designation VARCHAR(50)
);

INSERT INTO #Employee
VALUES ('Atul'    ,'Trainee')
      ,('Vishal'  ,'CEO')
      ,('Sangram', 'Onshore Head')
      ,('Niraj'   ,'VP')
      ,('Shailesh','CFO')
      ,('Chetan'  ,'Manager');

--Default Order of values
SELECT * 
FROM   #Employee;
	  
--Tweaking the order by clause with case statement
--to get result set as per our requirement.
SELECT Name, Designation
FROM   #Employee
ORDER BY CASE WHEN Designation = 'CEO'          THEN 0 
	      WHEN Designation = 'CFO'          THEN 1
	      WHEN Designation = 'VP'           THEN 2
	      WHEN Designation = 'Manager'      THEN 3
	      WHEN Designation = 'Onshore Head' THEN 4
	      WHEN Designation = 'Trainee'	THEN 5
	      ELSE 6
         END, Name;

3.

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 Gender - Case Statement In Order By Clause

USE tempdb;

IF OBJECT_ID('tempdb..#Player') IS NOT NULL
DROP TABLE #Player;
GO

CREATE TABLE #Player
(
 ID		INT,
 Name	VARCHAR(250),
 Gender CHAR(1)
);

INSERT INTO #Player
VALUES 
 (1, 'Andre' ,'M')
,(2, 'Steffi','F')
,(3, 'Pete'  ,'M')
,(4, 'Monica','F');

--Default Female players sorted first
SELECT   * 
FROM     #Player
ORDER BY Gender;

--Tweak Order By to Sort and Display Male Player First
SELECT   * 
FROM     #Player
ORDER BY CASE WHEN Gender='M' THEN 0 ELSE 1 END, Gender;

4.

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.

-- Sort Using Specific Columns - Case Statement In Order By 
USE tempdb;

IF OBJECT_ID('tempdb..#Player') IS NOT NULL
DROP TABLE #Player;
GO

CREATE TABLE #Player
(
 ID       INT,
 Name	  VARCHAR(250),
 Gender   CHAR(1),
 Country  VARCHAR(50)
);

INSERT INTO #Player
VALUES 
 (1, 'Andre' ,'M', 'United States')
,(2, 'Steffi','F', 'Germany')
,(3, 'Roger' ,'M', 'Swiss')
,(4, 'Monica','F', 'Yugoslavia');

SELECT   * 
FROM     #Player;

--Tweak Order By to Sort by specific column
--based on condition
SELECT   * 
FROM     #Player
ORDER BY CASE WHEN Gender='M' THEN Country ELSE Name END, Gender;

Summary

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.