0

I'm trying to pull information from the most recent 20 box scores for players on a particular team. I look up the teams roster, and pull relevant information for all the games those players have played in. I want the most recent 20 games of a player, for each player. I've run into difficulties with LIMITs and GROUPs. I've read an answer on here about creating a rank column with user defined variables, and then querying for the relevant ranks, but I cannot get the ranking piece to work.

It currently ranks every line a 1.

The logic should be that for a player, it ranks the most recent date 1, then counts upwards as the row results go backwards in time. The rank should reset to 1 for the a new player then count again as necessary. I would then query the results and limit to ranks <21.

Any help is greatly appreciated!

SET @currcount = NULL, @currvalue = NULL;

SELECT 
league_roster.player_name,

player_boxes.date, player_boxes.minutes, player_boxes.FG, 
player_boxes.FGA, player_boxes.3P, player_boxes.3PA, player_boxes.ft, player_boxes.fta, player_boxes.orb,
player_boxes.drb, player_boxes.tov, 

matchup_teamtotals.game_minutes, matchup_teamtotals.team_possessions,
matchup_teamtotals.opponent_possessions, matchup_teamtotals.possible_DRB, matchup_teamtotals.possible_ORB,
league_roster.team,

@currcount := IF(@currvalue = player_name, @currcount+1, 1) AS rank,
@currvalue := player_name AS whatever

FROM league_roster
INNER JOIN player_boxes ON player_boxes.player = player_name
INNER JOIN matchup_teamtotals ON matchup_teamtotals.matchup_id = player_boxes.matchup_id

WHERE league_roster.team = "Philadelphia 76ers"
ORDER BY player_name, date DESC




current output:
player:               date:           other fields              rank:
Tim Blackburn        Dec 31 2015       blah blah blah        1
Tim Blackburn        Dec 28 2015       blah blah blah        1
Tim Blackburn        Dec 27 2015       blah blah blah        1
Randy Johnson        Dec 31 2015       blah blah blah        1
Randy Johnson        Dec 27 2015       blah blah blah        1

desired output:
player:               date:           other fields              rank:
Tim Blackburn        Dec 31 2015       blah blah blah        1
Tim Blackburn        Dec 28 2015       blah blah blah        2
Tim Blackburn        Dec 27 2015       blah blah blah        3
Randy Johnson        Dec 31 2015       blah blah blah        1
Randy Johnson        Dec 27 2015       blah blah blah        2
tim blackburn
  • 1
  • 1
  • Example data, output, and desired output would be helpful. [sqlfiddle](http://sqlfiddle.com/)s are awesome to the max. There's a missing comma after `league_roster.team`, but I'm not sure if that's an error just in the post, or the actual fault you're experiencing, without any "this is the error/response I'm getting". – Will Jul 23 '16 at 22:19
  • edited the question to include examples of current and desired output. I am able to get the rank logic to work on a simple example without inner joins but cannot get it to work in the above example where I am piecing together information across the 3 tables. the 'other fields' are all returning numbers. – tim blackburn Jul 24 '16 at 13:28
  • Hmm, I started this [sqlfiddle](http://sqlfiddle.com/#!9/e6958/1) based on the added information, seems to work. What version of MySQL are you running? Is there any particular clause you add to cause it to stop working? – Will Jul 25 '16 at 04:05

0 Answers0