More readable:
SELECT
c.Name, Competition, ROUND(AVG(c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6),2) AS "Average Score",
CASE
WHEN Competition = 'Comp1' THEN ROUND(AVG(((c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6)/400)*100),2)
WHEN Competition = 'Comp2' THEN ROUND(AVG(((c.Phase1+c.Phase2+c.Phase3+c.Phase4+c.Phase5+c.Phase6)/600)*100),2)
ELSE 'Unspecified'
END AS "Average as Percent"
FROM Comp_Table c
JOIN (
SELECT a.ID
FROM Comp_Table a
JOIN comp_Table b
ON a.Name = b.Name
AND (a.Phase1+a.Phase2+a.Phase3+a.Phase4+a.Phase5+a.Phase6) <=
(b.Phase1+b.Phase2+b.Phase3+b.Phase4+b.Phase5+b.Phase6)
AND a.Competition = b.Competition
WHERE Year(a.date)=2016
GROUP BY a.ID, a.Competition
HAVING COUNT(*) <= 3
) AS x ON c.ID = x.ID
GROUP BY c.Competition, c.Name
HAVING COUNT(*) > 2
ORDER BY Competition, "Average Score" DESC
> This works apart from it uses the three latest scores not the three highest scores
It orders by `competition` ascending, score descending. That's not what you want?
The subquery doesn't aggregate; what's the point of its Group By?