Posted 13 April 2006 - 10:58 PM
Here is the problem...the temp can hold several records per player and even multiple for each year...For example:
player_id, year, position_id, games
4323, 2005, 5, 66
4323, 2005, 4, 20
4323, 2005, 3, 7
This example is for the player_id (4323) for 2005. This player played three different positions (3,4,5). What I want to do , if possible, is to find the position that player played the most games at for that given year and update the players table with that position_id.
Posted 14 April 2006 - 05:58 AM
SELECT position_id, COUNT(games) AS cnt FROM players GROUP BY player_id ORDER by cnt DESC LIMIT 1
So it should be easily to convert this to an update.
Posted 14 April 2006 - 04:40 PM
I've been trying to tweak the query you posted, but haven't been able to get the results I'm looking for.
If I use this query
SELECT position_id, COUNT( games ) AS cnt FROM temp GROUP BY player_id ORDER BY cnt DESC LIMIT 1;
it returns the position_id 3 with a count of 94.
I guess I need a little more help, so I can isolate the count to a specific player for a specific year
Posted 14 April 2006 - 08:46 PM
UPDATE players p INNER JOIN ( SELECT t.id, MIN(t.position_id) as position_id, t.year FROM temptable t INNER JOIN ( SELECT id, year, MAX(games) as mgames FROM temptable GROUP BY id, year ) s ON s.mgames=t.games AND s.id=t.id AND s.year=t.year GROUP BY t.id, t.year ) f ON p.id=f.id AND p.year=f.year SET p.position_id=f.position_id
There remains one question of behavior - If I played shortstop and second base exactly 30 games each in 2005, which position goes in the players table? I set it up there to take the MIN position, so whichever position has a lower position_id will take preference.
Posted 15 April 2006 - 02:23 AM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users