Sql Server: Case statement to Sort records by different columns of different data types

IntroductionHere in this article I am going to explain how to use CASE statement in Order by clause to sort records with different columns of different data types based on condition.

As we know we can use CASE statement in order by clause to sort data based on condition.But CASE expression can only have one data type, then how to sort with different columns of different data types? Here i have explained the solution. 

In previous articles i have explained How to use case expression in order by clause to sort records
 and How to CASE statement inside in clause (alternative) and ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() RANKING functions in SQL and SQLBULKCOPY to bulk insert multiple records from gridview to sql database in asp.net c#,vb and Update existing column values with linear serial numbers without loop

Implementation: Let's understand this by suitable example.

Create a temporary table using following script.

IF OBJECT_ID('tempdb.dbo.#tbStudent', 'U') IS NOT NULL

  DROP TABLE #tbStudent;

GO
CREATE TABLE #tbStudent
(
  StudentId                 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  StudentName          VARCHAR(100),
  DateOfBirth              DATE,
  RollNumber             INT,
  Section                     VARCHAR(5)
)

--Insert some dummy data into this table

GO
INSERT INTO #tbStudent(StudentName,DateOfBirth,RollNumber,Section)
VALUES
('Aman','2012-07-23',5,'A'),
('Jatin','2011-08-13',1,'C'),
('Anju','2012-02-09',2,'A'),
('Ankush','2010-11-03',6,'B'),
('Vivek','2013-06-30',3,'B'),
('Swati','2013-04-27',7,'C'),
('Varun','2012-07-15',8,'A'),
('Ranbeer','2010-12-14',4,'A'),
('Alia','2013-02-19',9,'B'),
('Virat','2011-03-01',11,'A'),
('Rohit','2012-09-15',10,'C')

--View dummy data

GO
SELECT * FROM #tbStudent           

StudentId
StudentName
DateOfBirth
RollNumber
Section
1
Aman
2012-07-23
5
A
2
Jatin
2011-08-13
1
C
3
Anju
2012-02-09
2
A
4
Ankush
2010-11-03
6
B
5
Vivek
2013-06-30
3
B
6
Swati
2013-04-27
7
C
7
Varun
2012-07-15
8
A
8
Ranbeer
2010-12-14
4
A
9
Alia
2013-02-19
9
B
10
Virat
2011-03-01
11
A
11
Rohit
2012-09-15
10
C

Now suppose we want to sort table records based on condition i.e. if we pass 1 in the @Order variable then it should sort records alphabetically based on student name and if we pass 2 then it should sort records alphabetically based on Student section.

The query for this can be written as:

First Run:

GO
DECLARE @Order INT;
SET @Order =1

SELECT * FROM #tbStudent
ORDER BY
CASE
  WHEN @Order = 1 THEN StudentName
             WHEN @Order = 2 THEN Section
END

Records will be sorted alphabetically based on student name as:
StudentId
StudentName
DateOfBirth
RollNumber
Section
9
Alia
2013-02-19
9
B
1
Aman
2012-07-23
5
A
3
Anju
2012-02-09
2
A
4
Ankush
2010-11-03
6
B
2
Jatin
2011-08-13
1
C
8
Ranbeer
2010-12-14
4
A
11
Rohit
2012-09-15
10
C
6
Swati
2013-04-27
7
C
7
Varun
2012-07-15
8
A
10
Virat
2011-03-01
11
A
5
Vivek
2013-06-30
3
B

Second Run:

GO
DECLARE @Order INT;
SET @Order =2

SELECT * FROM #tbStudent
ORDER BY
CASE 
             WHEN @Order = 1 THEN StudentName
             WHEN @Order = 2 THEN Section
END

Records will be sorted alphabetically based on student section as:
StudentId
StudentName
DateOfBirth
RollNumber
Section
3
Anju
2012-02-09
2
A
1
Aman
2012-07-23
5
A
7
Varun
2012-07-15
8
A
8
Ranbeer
2010-12-14
4
A
10
Virat
2011-03-01
11
A
9
Alia
2013-02-19
9
B
4
Ankush
2010-11-03
6
B
5
Vivek
2013-06-30
3
B
6
Swati
2013-04-27
7
C
2
Jatin
2011-08-13
1
C
11
Rohit
2012-09-15
10
C

But suppose we also want to sort student records based on DateOfBirth or RolNumber? The first thought that comes into mind is to simply add one case for DateOfBirth and one Case for RollNumber like in the query below:

GO
DECLARE @Order INT;
SET @Order =1 --or 2 works

SELECT * FROM #tbStudent
ORDER BY
CASE 
             WHEN @Order = 1 THEN StudentName
             WHEN @Order = 2 THEN Section
             WHEN @Order = 3 THEN DateOfBirth --issue here
             WHEN @Order = 4 THEN RollNumber --issue here
END

Try all cases by passing 1,2,3,4 in @Order variable, it will not work and it will generate the error as:

Msg 206, Level 16, State 2, Line 3
Operand type clash: int is incompatible with date

The reason is 'CASE expression can only have one data type'. If we use different data types in a case expression then highest one will be used to evaluate as per Data type precedence. Please check at the link https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

Then how to solve this issue?

Here is the solution.
When using a CASE expression in an order by clause then we have to deal with each data type in a separate expression rather than single case statement.

Let's re-write the query and test for each condition.

First Run:

GO
DECLARE @Order INT;
SET @Order =1

SELECT * FROM #tbStudent
ORDER BY
CASE WHEN @Order = 1 THEN StudentName END,
CASE WHEN @Order = 2 THEN Section END,
CASE WHEN @Order = 3 THEN DateOfBirth END,
CASE WHEN @Order = 4 THEN RollNumber END

Results will be sorted by StudentName as:
StudentId
StudentName
DateOfBirth
RollNumber
Section
9
Alia
2013-02-19
9
B
1
Aman
2012-07-23
5
A
3
Anju
2012-02-09
2
A
4
Ankush
2010-11-03
6
B
2
Jatin
2011-08-13
1
C
8
Ranbeer
2010-12-14
4
A
11
Rohit
2012-09-15
10
C
6
Swati
2013-04-27
7
C
7
Varun
2012-07-15
8
A
10
Virat
2011-03-01
11
A
5
Vivek
2013-06-30
3
B

Second Run:

GO
DECLARE @Order INT;
SET @Order =2

SELECT * FROM #tbStudent
ORDER BY
CASE WHEN @Order = 1 THEN StudentName END,
CASE WHEN @Order = 2 THEN Section END,
CASE WHEN @Order = 3 THEN DateOfBirth END,
CASE WHEN @Order = 4 THEN RollNumber END

Result will be sorted by Section as:
StudentId
StudentName
DateOfBirth
RollNumber
Section
3
Anju
2012-02-09
2
A
1
Aman
2012-07-23
5
A
7
Varun
2012-07-15
8
A
8
Ranbeer
2010-12-14
4
A
10
Virat
2011-03-01
11
A
9
Alia
2013-02-19
9
B
4
Ankush
2010-11-03
6
B
5
Vivek
2013-06-30
3
B
6
Swati
2013-04-27
7
C
2
Jatin
2011-08-13
1
C
11
Rohit
2012-09-15
10
C

Third Run:

GO
DECLARE @Order INT;
SET @Order =3

SELECT * FROM #tbStudent
ORDER BY
CASE WHEN @Order = 1 THEN StudentName END,
CASE WHEN @Order = 2 THEN Section END,
CASE WHEN @Order = 3 THEN DateOfBirth END,
CASE WHEN @Order = 4 THEN RollNumber END

Result will be sorted by DateOfBirth as:
StudentId
StudentName
DateOfBirth
RollNumber
Section
4
Ankush
2010-11-03
6
B
8
Ranbeer
2010-12-14
4
A
10
Virat
2011-03-01
11
A
2
Jatin
2011-08-13
1
C
3
Anju
2012-02-09
2
A
7
Varun
2012-07-15
8
A
1
Aman
2012-07-23
5
A
11
Rohit
2012-09-15
10
C
9
Alia
2013-02-19
9
B
6
Swati
2013-04-27
7
C
5
Vivek
2013-06-30
3
B

Fourth Run:

GO
DECLARE @Order INT;
SET @Order =4

SELECT * FROM #tbStudent
ORDER BY
CASE WHEN @Order = 1 THEN StudentName END,
CASE WHEN @Order = 2 THEN Section END,
CASE WHEN @Order = 3 THEN DateOfBirth END,
CASE WHEN @Order = 4 THEN RollNumber END

Results will be sorted by RollNumber as:
StudentId
StudentName
DateOfBirth
RollNumber
Section
2
Jatin
2011-08-13
1
C
3
Anju
2012-02-09
2
A
5
Vivek
2013-06-30
3
B
8
Ranbeer
2010-12-14
4
A
1
Aman
2012-07-23
5
A
4
Ankush
2010-11-03
6
B
6
Swati
2013-04-27
7
C
7
Varun
2012-07-15
8
A
9
Alia
2013-02-19
9
B
11
Rohit
2012-09-15
10
C
10
Virat
2011-03-01
11
A
 Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..