This is a follow on from my last post.
Here is my current query:
SELECT * FROM ( SELECT * FROM ( SELECT TeamID, PlayerID, COUNT(*) AS Total FROM Scored WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012 GROUP BY PlayerID ) T GROUP BY TeamID, Total DESC ) T GROUP BY TeamID, Total DESC
Giving me as the result:
TeamID PlayerID Total A James 34 A Peter 28 A Amy 23 A Carl 3 A Sam 1 B David 8 B Steven 7 B Claire 4 B Andrew 3 C John 29 C Robert 28 C Angela 18 C Sarah 14 C Tom 12 C Craig 11 C Zach 10 C Gregory 8 C Larry 4 C Curly 2 C Moe 1
What I'm after is to select only the second highest scorer from each TeamID, and if a team were to have only one player then that player is not selected.
Now what I also have is:
SELECT TeamID, PlayerID, MAX(Total) AS Total FROM ( SELECT TeamID, PlayerID, COUNT(*) AS Total FROM Scored WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012 GROUP BY PlayerID ORDER BY Total DESC ) T GROUP BY TeamID
which gives me a selection of only the top scorer from each team.
So I was wondering if there was a way to compare the two results and remove the duplicate PlayerIDs so that I can then just select the MAX(total) once again from what is left behind?
NB: I am using Navicat for MySQL and am thus unable to create temporary tables.
Any help would be very much appreciated.