Use CASE statement with ORDER BY clause in SQL Server

Updated on August 24, 2015     Kisan Patel

This tutorial will show you how to use CASE statement with ORDER BY clause in SQL Server?

To do this, we use the CASE after ORDER BY and then checks for column value.

SELECT CompanyName,ContactName  FROM Customers
	ORDER BY
		CASE IsActive WHEN 1 THEN IsActive END ASC,
		CASE WHEN IsActive = 0 THEN ContactName
	        ELSE CompanyName END DESC

In above case, all records having Active = 1 is sorted on “Active ASC” order. All records having Active = 0 is sorted on ContactName DESC else CompanyName DESC order.

Now look at the below statements, where we have just changed the last word from DESC to ASC.