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 

Ph

You are generating this by your query, so what's the matter? – Diplomatics

@AlirezaMirzaeiyan - A union doesn't provide any guarantee of order. – Lorenz

Does Name participate in the order? Both your explanation and sample data indicate that you only care about Role. All of the current answers incorrectly include Name in the order by clause. – Lorenz

@Lorenz yes it Does, when you see my sample result it is sorted alphabetically. within their role group – Ph

2, D is followed by 2, B in your sample results. Which alphabet are you sorting by? – Lorenz

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

Steep

This gets you all "3s" in alphabetical order, then all "not 3s" in alphabetical order. – Plastered

Answer 2

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

Peahen

Answer 3

I usually use NULLIF, but case might be faster?

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

Prominence

Answer 4

select * from table order by FIELD(id, 3) DESC, id

Touchandgo