CASE in ORDER BY clause in SQL Server

How to use CASE with ORDER BY clause in SQL Server?

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

SELECT * FROM PersonalDetails
ORDER BY
       CASE Active WHEN 1 THEN Active END ASC,
       CASE WHEN Active = 0 THEN LastName
       ELSE FirstName END DESC

In above case, all records having Active = 1 is sorted on “Active ASC” order. All records having Active = 0 is sorted on ‘LastName DESC’ else ‘FirstName DESC’ order.

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

SELECT * FROM PersonalDetails
ORDER BY
       CASE Active WHEN 1 THEN Active END ASC,
       CASE WHEN Active = 0 THEN LastName
       ELSE FirstName END ASC

And the order of records having Active = 0 has changed. Notice the highlighted records in below picture.