On the surface, the top n per group query is a fairly common one and I have used a few variations on other projects. However, I really cannot get my head around this one and I think it is due to the number of joins.
My query (at the bottom of the post) is trying to find the highest 3 scoring players per week. The score field is PPP.
So, in theory, I am looking at grouping by week, then by player (to get the sum of his scores), find the 3 players with the highest PPP per week and them sort them by PPP desc along with, ideally, giving them a rank number.
I know I would be able to do something that works with php but as I am sure doing it all in a query would be more server-friendly, I am determined to get this right.! The query below is not the only attempt I have had but it is the closest I have got to it working. The most often found solution LEFT OUTER JOIN table t2 ON (t1.id = t2.id AND t1.date < t2.date) did not come anywhere near working.
The parts of the output are correct are the grouping by YearWeek and User and the sum of that users PPP. It also does correctly select 3 users from each week (as long as there are 3 available) but not the highest scoring 3 and not in correct rank order.
So, in my head, I feel like I am close to getting it right but obviously may be a mile away! I have attached an SQL dump of the tables and data in case they will help.
Thanks in advance for any suggestions.
Select x.PLYR, x.PPP, x.WST, x.WND, x.rank, x.YWK From (Select U.username PLYR, Sum(Case When P.home = G.hgoals And P.away = G.agoals Then 3 When (P.home > P.away And G.hgoals > G.agoals) Or (P.home < P.away And G.hgoals < G.agoals) Or (P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1 Else 0 End) PTS, Sum(Case When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null And G.agoals Is Not Null Then 1 Else 0 End) TOT, Sum(Case When P.home = G.hgoals And P.away = G.agoals Then 3 When (P.home > P.away And G.hgoals > G.agoals) Or (P.home < P.away And G.hgoals < G.agoals) Or (P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1 Else 0 End) / Sum(Case When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null And G.agoals Is Not Null Then 1 Else 0 End) PPP, Date_Add(G.date, Interval (0 - WeekDay(G.date)) Day) WST, Date_Add(G.date, Interval (6 - WeekDay(G.date)) Day) WND, Week(G.date) WK, CASE WHEN @currweek != YearWeek(G.date, 1) THEN @rank := 1 ELSE @rank := @rank + 1 END AS rank, @currweek := YearWeek(G.date, 1) YWK From compuser CU Inner Join comps C On C.compid = CU.comp Left Join users U On U.userid = CU.user Inner Join compgame CG On C.compid = CG.comp Inner Join games G On G.gamesid = CG.game Left Join predictions P On CU.user = P.user And CG.game = P.game JOIN (SELECT @rank := NULL, @currweek := '') r Where G.date < Now() Group By YearWeek(G.date, 1), U.userid Order By YearWeek(G.date, 1), PPP Desc) x Where x.rank <= 3