Specific Query with Two Tables
Posted 12 October 2006 - 04:19 PM
SELECT OutcomeID, Code1 FROM Play_Outcome_Log, Play_Log WHERE Play_Outcome_Log.OutcomeID=5 AND Play_Outcome_Log.PlayID = Play_Log.PlayID AND Play_Log.PlayCode='O'
I am trying to run a query that tells me the player number with the most 2-point baskets on the team (in this example). The OutcomeID represents the code of the play. "5" is for a 2-point basket. Since I want it for the main team, I choose 'O' for PlayCode meaning offensive. Here is what was returned:
I have not added all of the plays in, but at this point, player #3 is the one with the most 2-point baskets. How can I change the query so I only receive this one number as my result (the player number with the most 2-point baskets)? Hopefully I have explained this clearly.
Posted 12 October 2006 - 06:27 PM
SELECT Code1, COUNT(*) AS cnt FROM Play_Outcome_Log INNER JOIN Play_Log ON Play_Outcome_Log.PlayID = Play_Log.PlayID WHERE Play_Outcome_Log.OutcomeID=5 AND Play_Log.PlayCode='O' GROUP BY Code1 ORDER BY cnt DESC LIMIT 1
Posted 12 October 2006 - 07:57 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users