SQL; How to write "Order By" statement that it will switch up whenever it found the first match

Question

I apologize if this question have been made before.

This is my original table.

user_name	Usertype	Team
Benjamin Turner	Support	Cat Welfare Society
Luis Bennett	Main	Cat Welfare Society
Benjamin Turner	Support	Andal Youths
Benjamin Turner	Support	ACaretalyst
Cynthia Bender	Main	Volunteer Centre
Benjamin Turner	Support	Volunteer Centre

I want to rearrange this using Order by, so that Main account will appear first, follow by the Support account of the same team. Those other teams that Benjamin support but there is no Main account, will be added in at the bottom.

"EXPECTED RESULT"

user_name	Usertype	Team
Luis Bennett	Main	Cat Welfare Society
Benjamin Turner	Support	Cat Welfare Society
Cynthia Bender	Main	Volunteer Centre
Benjamin Turner	Support	Volunteer Centre
Benjamin Turner	Support	Andal Youths
Benjamin Turner	Support	ACaretalyst

I have tried "Order by usertype , team" which results in listing for all the Main account first ,THEN support account (meaning Main and Support of the same team do not stick together)

user_name	Usertype	Team
Luis Bennett	Main	Cat Welfare Society
Cynthia Bender	Main	Volunteer Centre
Benjamin Turner	Support	Cat Welfare Society
Benjamin Turner	Support	Volunteer Centre
Benjamin Turner	Support	Andal Youths
Benjamin Turner	Support	ACaretalyst

On other hands, if I tried "Order by team, usertype" then Main account and Support account stick together, but the team without any Main , will then appear first ( due alphabetical order)

user_name	Usertype	Team
Benjamin Turner	Support	ACaretalyst
Benjamin Turner	Support	Andal Youths
Luis Bennett	Main	Cat Welfare Society
Benjamin Turner	Support	Cat Welfare Society
Cynthia Bender	Main	Volunteer Centre
Benjamin Turner	Support	Volunteer Centre

Is there any way to structure the order by so that the first row is always the Main Account first, then follow by related Support of the same team ( regardless of the alphabetical order of the Team) Then whatever remaining Support accounts (without any Main account) will be added in bottom

Answer 1

So we want to sort according by these rules

• Complete Teams (which has a user with Main UserType) first
• Then by Team (to ensure Main and Support go together)
• Finally, within each Team sort by UserType: Main, Support and all the rest

We can try doing it as

with MyUsers as (
  select user_name,
         Usertype,
         Team,
         case 
           when exists (select 1 
                          from MyTable m 
                         where m.Team = Team 
                           and m.Usertype = 'Main') then 1
           else 2
         end CompleteGroupOrder,     
         case 
           when Usertype = 'Main' then 1
           when Usertype = 'Support' then 2
           else 3
         end TeamOrder
    from MyTable)

  select user_name,
         Usertype,
         Team
    from MyUsers
order by CompleteGroupOrder,
         Team, 
         TeamOrder