SQL Server – Order By Clause Based on a Variable

This is a guest post from one of my good friend and Developer Ranjoyt Singh. He is a dedicated and committed person professionally and personally.

We developers come across many times in a situation where we need to change the sort order in which results of a query are displayed. Sorting based on a variable can be achieved in the application as well as in T-SQL. The SQL Server gives performance benefit over application. So, let’s explore what are the constraints and how we might resolve them.

• The first constraint is that we can use “case” statement in the order by clause and not the “if” statements.

Let’s begin with creating a table with test data.

CREATE TABLE Customer
(
	id		INT,
	CustId		INT,
 	Name		VARCHAR(30),
 	Address		VARCHAR(50),
 	FromDate	DATETIME,
 	ToDate		DATETIME
 )

 GO 

 INSERT INTO Customer
	VALUES (1, 1, 'Elissa','# 13 Vancouver, CA','01 Jan 2010','01 May 2012');

 INSERT	INTO Customer
	VALUES (2, 2, 'Melissa','# 15 Ney York, USA','01 Mar 2009','01 Dec 2011');		

 INSERT	INTO Customer
	VALUES (3, 3, 'Alice','15-1 Chicago, USA','01 Feb 2002','01 July 2015');

 INSERT	INTO Customer
	 VALUES (4, 4, 'Robert','1/2 New Jerssey, UK','01 Jan 2003','01 Aug 2005');

 INSERT	INTO Customer
	VALUES (5, 5, 'John','23-2-2 Tokyo, China','01 Mar 1999','01 July 2012');

 GO

 

SELECT * FROM Customer

 

Let us first try to use the if statement in a order by clause. We get error like the one shown below:

 

 

Now what we want to achieve can be done as shown below:

 

---When We Simply want to sort on different columns of same data type---

DECLARE @Col VARCHAR(15)

SET @Col = 'Name'

SELECT * FROM customer
ORDER BY CASE (@Col) WHEN 'Name' THEN Name
		 ELSE Address
 END

 

 

• The second constraint is that we cannot define ascending or descending based on a variable directly.

The following example shows how we can use case and achieve multiple sort direction and columns simultaneously

 

---When We want to change sort direction and variable based on a variables---

DECLARE @col VARCHAR
DECLARE @AscOrDsc VARCHAR(5)

      SET @AscOrDsc = 'Desc'
      SET @col = 'Address'

SELECT * FROM customer
ORDER BY
	CASE @AscOrDsc WHEN 'Desc' THEN
		  CASE @col WHEN 'Name' THEN Name
		  ELSE Address
		  END
	END DESC,
	CASE @AscOrDsc WHEN 'Asc' THEN
			CASE @col WHEN 'Name' THEN Name
			ELSE Address
			END
	END

 

 

• The third constraint is that the data type of variable should be same if we want to use a variable in a order by clause

 

Let’s understand this scenario with an example:

DECLARE @col VARCHAR(20) 

SET @col = 'FromDate'

SELECT * FROM customer
    ORDER BY CASE @col   WHEN 'Name' THEN Name
	     WHEN 'FromDate' THEN FromDate
     WHEN 'ToDate' THEN ToDate
	     WHEN 'Id'	THEN id
	     WHEN 'CustId' THEN CustId
		 ELSE Address
    END

 

I get the following results:

 

 

Now I change the value of the same variable to Name (a column of type Text) from the current value FromDate (a column of type DateTime). I get the error as shown below:

 

The error surfaces only when we run the same query second time for a variable referring to a column of different type in same order by clause. This constraint can be overcome as shown below:

    ----When We want to  sort diffrent data type coloumns based variables---

DECLARE @col VARCHAR(20) 

SET @col = 'FromDate'	

IF (@col = 'Address' OR @col = 'Name' )
BEGIN
		SELECT * FROM customer
		ORDER BY CASE (@Col) WHEN 'Name' THEN Name
			 ELSE Address
		END
END
ELSE IF (@col = 'Id' OR @col = 'Custid')
BEGIN
		SELECT * FROM customer
		ORDER BY CASE (@Col) WHEN 'id' THEN Id
			 ELSE CustId
		END
END
ELSE IF ( @col = 'FromDate' OR @col = 'ToDate' )
BEGIN
		SELECT * FROM customer
		ORDER BY CASE (@Col) WHEN 'FromDate' THEN FromDate
			 ELSE ToDate
		END
END

I hope this information is helpful to you. In case you have any queries or feedback. You can contact me on ranjoyt@gmail.com