Live results

select 
  psc_scores_M4EEO23AF.stage_number
  ,psc_def_M4EEO23AF.sh_dvp
  ,psc_scores_M4EEO23AF.ipm_hf
  ,(
    select max(ipm_hf) 
    from psc_scores_M4EEO23AF MAXHFT 
    join psc_def_M4EEO23AF MAXHFTSHT on (MAXHFTSHT.sh_uid=MAXHFT.shtr) 
    where 
      MAXHFT.stage_number=psc_scores_M4EEO23AF.stage_number 
      and
      MAXHFTSHT.sh_dvp=psc_def_M4EEO23AF.sh_dvp
      and
      MAXHFTSHT.sh_dq<>1  
  ) as maxHF
  ,psc_scores_M4EEO23AF.*
  ,psc_def_M4EEO23AF.*
  ,typenonteamcategory.TypeNonTeamCategory
from psc_scores_M4EEO23AF
join psc_def_M4EEO23AF on (psc_def_M4EEO23AF.sh_uid=psc_scores_M4EEO23AF.shtr and `psc_def_M4EEO23AF`.sh_del=0 )
join match_shooters on (match_shooters.shoMatId=10 and match_shooters.shoCompId=psc_def_M4EEO23AF.sh_num)
left join typenonteamcategory on (typenonteamcategory.TypeNonTeamCategoryId=match_shooters.shoCategory)
where 
  sh_dq<>1
order by  psc_scores_M4EEO23AF.ipm_hf DESC, maxHF desc