Introduction: Here in this article I am going to explain how to use
CASE statement in Order by clause to sort records as per requirement.
Implementation: Let's understand this by suitable
example.
In previous articles I have explained How to use Case statement to sort records by different columns of different data types and How to use case statement inside in clause (alternative) and Update table data using inner join in sql server and Using case expression in select statement in sql and Sql statement equivalent to ternary/conditional operator and Try catch to handle exception and commit/rollback transaction in sql
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),
Gender CHAR(1)
)
--Insert
some dummy data into this table
GO
INSERT INTO #tbStudent(StudentName,Gender)
VALUES
('Aman','M'),
('Jatin','M'),
('Anju','F'),
('Ankush','M'),
('Vivek','M'),
('Swati','F'),
('Varun','M'),
('Ranbeer','M'),
('Aalia','F'),
('Virat','M'),
('Rohit','M');
----View
dummy data
GO
SELECT * FROM #tbStudent
Result will be as:
StudentId
|
StudentName
|
Gender
|
1
|
Aman
|
M
|
2
|
Jatin
|
M
|
3
|
Anju
|
F
|
4
|
Ankush
|
M
|
5
|
Vivek
|
M
|
6
|
Swati
|
F
|
7
|
Varun
|
M
|
8
|
Ranbeer
|
M
|
9
|
Aalia
|
F
|
10
|
Virat
|
M
|
11
|
Rohit
|
M
|
Now suppose it is required to sort Male students above
Female students.
If we run a query with default ORDER BY clause i.e.
ORDER BY Gender then it will logically sort Female students before Male
students considering alphabetical order of character F before M.
Let’s try to see this by executing the following
query:
SELECT * FROM
#tbStudent ORDER BY
Gender
Result will be as:
StudentId
|
StudentName
|
Gender
|
3
|
Anju
|
F
|
6
|
Swati
|
F
|
9
|
Aalia
|
F
|
10
|
Virat
|
M
|
11
|
Rohit
|
M
|
7
|
Varun
|
M
|
8
|
Ranbeer
|
M
|
4
|
Ankush
|
M
|
5
|
Vivek
|
M
|
1
|
Aman
|
M
|
2
|
Jatin
|
M
|
As we can see Female student appeared on the top of Male
students
What if we want the Male students to be listed first
than female students? We can do this using CASE statement in order by clause
as:
SELECT * FROM #tbStudent
ORDER BY
CASE WHEN Gender = 'M' THEN 0
WHEN
Gender = 'F' THEN 1
END
--Sorted result will be as:
StudentId
|
StudentName
|
Gender
|
1
|
Aman
|
M
|
2
|
Jatin
|
M
|
4
|
Ankush
|
M
|
5
|
Vivek
|
M
|
7
|
Varun
|
M
|
8
|
Ranbeer
|
M
|
10
|
Virat
|
M
|
11
|
Rohit
|
M
|
9
|
Aalia
|
F
|
6
|
Swati
|
F
|
3
|
Anju
|
F
|
As we can see result is as per our requirement.
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.
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..