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.
Create a temporary table using following script.
Now over to you:
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
In previous articles i have explained How to use case expression in order by clause to sort records
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 = 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 = 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
|
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.
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..