SQL Server : Using CASE expression in an ORDER BY clause

Many times, I used to hear the question from my folks that “How can I use CASE in an ORDER BY clause?”

Let me show you, how CASE expression can be used in an ORDER BY clause to determine the sort order of the rows based on a given column value.

Example 1 :

Let us assume that we need to sort [CityName] based on @ORDER variable when we set to 1, the result set should sort by [CityName] in ascending order and @ORDER set to 2 should be in descending order.

Normal result without order by clause would be :

SELECT [CityID],[CityName] FROM [DBO].[City]

I have modified the above statement slightly to make ORDER BY based on the condition which is shown below:

DECLARE @ORDER TINYINT
SET @ORDER = 1
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ORDER = 1 THEN [CityName] END ASC,
CASE WHEN @ORDER = 2 THEN [CityName] END DESC

Let us execute the above statement and see the output.

From the above result set, we could identify that the records were sorted by [CityName] in ascending order as @ORDER value is 1.

Now, let us change @ORDER value to 2 (descending order) and see the result.

DECLARE @ORDER TINYINT
SET @ORDER = 2
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ORDER = 1 THEN [CityName] END ASC
CASE WHEN @ORDER = 2 THEN [CityName] END DESC
GO

As expected, the result set has been sorted by [CityName] in descending order.

Example 2 :

The same CASE expression can also be used to sort columns based on conditions. See the below example.

DECLARE @ColumnOrder VARCHAR
SET @ColumnOrder = 'CityName'

SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ColumnOrder = 'CityName' THEN[CityName] END ASC,
CASE WHEN @ColumnOrder = 'CityID' THEN [CityID]END ASC
GO

The same statement can be used in Stored Procedure as given below :

CREATE PROCEDURE [DBO].[STP_City]
(
@ColumnOrder VARCHAR(10)
)
AS
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ColumnOrder = 'CityName' THEN[CityName] END ASC,
CASE WHEN @ColumnOrder = 'CityID' THEN [CityID]END ASC
GO

EXECUTE [DBO].[STP_City] 'CityName'
GO

Note : For this example, I have used only variables, even column names also be used in place of variables.