Sql Server: Using case expression in order by clause to sort records

IntroductionHere 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.

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.
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..