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

Question

I apologize if this question has been asked before. This is my original form. Username User Type Team Benjamin Turner Support Cat Welfare Association Louise Bennett Major Cat Welfare Association Benjamin Turner Support Andal Youth Benjamin Turner Support ACaretalyst Cynthia Bender Major 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 Result" username usertype teamlouie

P粉614840363 · Answer

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

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