Order by Specific id first then By rest

Question

Consider a Sample Table with two Column 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 following Output.

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

I just want to Order by Role Id 3 first then by remaining Roleid. Currently i am using Union to achieve so //

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

Answer 1

You can use case to make more complex ordering:

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