SELECT s.reg_id,name,s.total vault1,g.total vault2,s.subtotal evault1,g.subtotal evault2, round((s.total+g.total)/2,2) avg1,GREATEST(s.total,g.total) maxvault,GREATEST(s.subtotal,g.subtotal) maxe,club FROM `gymnast_score` s INNER join registrations r on s.reg_id=r.id left join apparatus a on a.apid=s.apid LEFT join (select * from gymnast_score g where eventid='9' and apid='17' and category='SubJunior') g on g.reg_id=s.reg_id where s.eventid='9' and a.apid='6' and r.gender='F' and s.category='SubJunior' ORDER BY avg1 DESC,maxvault DESC,maxe DESC