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

Introduction: Here 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.

• Tweet
• Share
• Share
• Share

Related Post