Regarding a football (soccer) league:
I am trying to find the top goalscorer for each team in the league in December 2012.
Each time a player scores a new row is created in the table "Scored" with their name in it and their corresponding team and the date the goal was scored.
The query I have so far is as follows:
SELECT TeamID, PlayerID, COUNT(*) AS Total FROM Scored WHERE MONTH(GoalDate)=12 AND YEAR(GoalDate)=2012 GROUP BY PlayerID
This gives me the total for each player but anything I try to find only the top goal scorer for each team has not worked. I am using Navicat for MySQL and apparantly I can't create temporary tables using it.
Does anyone have any suggestions?