Help with Query Please

[m4tthall]

Firstly, I will post the query

 

SELECT ts_game_league.game_id, ts_game_league.league, MAX(ts_table.week), ts_table.user_id, MAX(ts_table.total_points) FROM ts_game_league LEFT JOIN ts_table ON ts_game_league.game_id = ts_table.game_id AND ts_game_league.league = ts_table.league GROUP BY ts_game_league.game_id, ts_game_league.league

 

Next, this is the results it gives me:

 

game_id 	league 	week 	user_id 	MAX( ts_table.total_points )
1 	1 	39 	78 	415
2 	1 	1 	1 	50

 

This works, except the user_id is not what I would want. Basically, this query should return the maximum total points for the last week (which is essentially an incrementing number) and the user that got the maximum points for each game and league. I know the query is wrong, but I cannot think of a way to do what I want in one query, but sure it must be possible some how?

 

A bit of an explanation of the fields above:

 

• game_id - part of composite key (with league)
  
• league - part of composite key (with game_id)
  
• week - an incrementing number that shows the week of the game/league
  
• user_id - a foreign key to identifier of user that got the score
  
• total_points - the total points the user got for the game/league/week
  

 

If anyone could offer any advice I would be most grateful. 

[fenway]

You can't ask MAX() to do something other than get the maximum value -- it can't magically figure out which user has this maximum value.  You'll need to get the list of users and max's first, then join this to your primary query.

[m4tthall]

Thanks for the reply, but that is exactly what I am struggling with. How do I do that?

 

Thanks again.

[fenway]

Post your table structure -- but you need a simply group by with max() on the table.

[m4tthall]

ts_game_league

============

game_id (PK)

league (PK)

 

ts_table

======

week

user_id

total_points

game_id (FK from ts_game_league)

league (FK from ts_game_league)

 

I need:

- game_id

- league

- maximum week for each game_id/league key

- total_points for each game_id/league key

- user_id of user that got the total_points

 

Example:

game_id - 1

league - 2

week - 10

total_points - 50

user_id - 24

 

game_id - 1

league - 3

week - 8

total_points - 55

user_id - 20

 

game_id - 2

league - 1

week - 10

total_points - 65

user_id - 22

 

Thanks again

[fenway]

And you want the max points per game_id?

[m4tthall]

And you want the max points per game_id?

 

The max total points, the user that got the max total points, for the highest week (scores are accumulative) per game id/league pairing (which is the key).

[fenway]

OK... so run

 

select game_id, league, max(total_points) as maxpoints

from ts_table

group by game_id, league

 

and then join this result back to ts_table with all three of these columns as the on condition... then the users table should be another join.