Hi all,
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.
Thanks.