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

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. 

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

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.

• Tweet
• Share
• Share
• Share

Related Post