Order by specific identifier first

Question

Consider a sample table with two columns RoleId and User Name

Role | Name
  1      AB
  3      A
  1      ABC
  2      D
  2      B
  3      Abb
  1      E
  4      TE

How can I use SQL queries to get the following output.

Role | Name
  3      A
  3      Abb
  1      AB
  1      ABC
  1      E
  2      B
  2      D
  4      TE

I just want to complete the order by role ID 3 first, leaving the Roles role. I am currently using Union to achieve this //

SELECT * FROM (SELECT * From @temp 
         Where roleid=3
UNION ALL
SELECT * From @temp 
         Where roleid != 3
 ) as X 

Answer 1

You can use case for more complex ordering:

select *
 from @temp
 order by case when Role = 3 then 0 else 1 end, Role, Name

Answer 2

select *
from @temp
order by CASE WHEN Role = 3 THEN 0 ELSE Role END, Name

Answer 3

I usually use NULLIF, but maybe faster?

SELECT   *
FROM     @temp
ORDER BY NULLIF(Role,3), Name