SQL; How to write an "Order By" statement that switches as soon as the first match is found

Question

My apologies if this question has been asked before.

This is my original form.

username	user type	team
Benjamin Turner	support	Cat Welfare Association
Louis Bennett	main	Cat Welfare Association
Benjamin Turner	support	Andal Youth Team
Benjamin Turner	support	ACaretalyst
Cynthia Bender	main	Volunteer Center
Benjamin Turner	support	Volunteer Center

I want to rearrange it using sorting so that the main account comes first, then the support accounts for the same team. Other teams that Benjamin supports but does not have a master account will be added at the bottom.

"expected outcome"

username	user type	team
Louis Bennett	main	Cat Welfare Association
Benjamin Turner	support	Cat Welfare Association
Cynthia Bender	main	Volunteer Center
Benjamin Turner	support	Volunteer Center
Benjamin Turner	support	Andal Youth Team
Benjamin Turner	support	ACaretalyst

I already tried it "Sort by user type, team", which results in all master accounts being listed first, then support accounts (meaning master and support accounts for the same team won't be stuck together)

username	user type	team
Louis Bennett	main	Cat Welfare Association
Cynthia Bender	main	Volunteer Center
Benjamin Turner	support	Cat Welfare Association
Benjamin Turner	support	Volunteer Center
Benjamin Turner	support	Andal Youth Team
Benjamin Turner	support	ACaretalyst

On the other hand, if I try "Sort by Team, User Type", the Main and Support accounts are stuck together, but the Teams without any Main will appear first (alphabetically)

username	user type	team
Benjamin Turner	support	ACaretalyst
Benjamin Turner	support	Andal Youth Team
Louis Bennett	main	Cat Welfare Association
Benjamin Turner	support	Cat Welfare Association
Cynthia Bender	main	Volunteer Center
Benjamin Turner	support	Volunteer Center

Is there any way to structure the order so that the first row is always the main account first, then the related support for the same team (regardless of the alphabetical order of the teams) Any remaining support accounts (without any master account) will then be added to the bottom

Answer 1

So we want to sort based on these rules

• Complete firstTeam(Users with Main UserType)
• Then by the team (to ensure Main and Support are carried out together)
• Finally, sort by UserType within each Team: Main, Support, and all other
>

We can try this

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