ORDER BY specific values first in SQL Server

Question

I can not figure this one out for the life of me, please help. I am using SQL Server and I need help with the ORDER BY Clause.

I want this..

ID     Name
--------------
1      Orange
2      Yellow
3      Green
4      Purple
5      Red

To order like this

ID     Name
--------------
4      Purple   <-- This one first then ASC afterwards
3      Green
1      Orange
5      Red
2      Yellow        

Sort of something like

ORDER BY ID=4, Name ASC

I used to use field in MySQL but you can't use that in SQL Server.

Please help.

Answer 1

You can use CASE in the ORDER BY clause.

ORDER BY CASE WHEN ID = 4 THEN 0 ELSE 1 END ASC, 
         NAME ASC