Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Select avg of best 2 scores 2
- Thread starter QatQat
- Start date
-
2
- #2
okay, pick a row, any row, let's call it row X
the subquery now counts how many other rows, for the same player, have a higher score
if the number of rows that have a higher score for the same player is less than 2 (i.e. 1 or 0), then this row X has to be one of the top 2 (i.e. if the count is 0, then X is the highest score for the player, and if the count is 1, then X is the second highest score for the player)
thus the subquery acts as a filter, and ensures we're getting only the top 2 scores per player
the outer query then provides the average per player
helps?
r937.com | rudy.ca
the subquery now counts how many other rows, for the same player, have a higher score
if the number of rows that have a higher score for the same player is less than 2 (i.e. 1 or 0), then this row X has to be one of the top 2 (i.e. if the count is 0, then X is the highest score for the player, and if the count is 1, then X is the second highest score for the player)
thus the subquery acts as a filter, and ensures we're getting only the top 2 scores per player
the outer query then provides the average per player
helps?
r937.com | rudy.ca
I think I've got this. For argument's sake lets say player Dave has the following scores:
18,33,14,27,21,24
and lets say the query checks those rows as X.score in the order they appear here. I know it could be random which row is checked, but hey, it's my scenario.
so X.score=18 is checked and there are four rows with scores greater than it, so it gets discarded.
it would then move on to X.score=33 and find out there are no scores above that so that would be one of the rows that we would use.
It would then step through X.score=14 and discard it for the same reason as X.score=18.
It then steps through to X.score=27 and finds there is only one score greater than it so it must be the second highest score.
So then the scores are 33 and 27 and we now take the average of those two scores to find that Dave has an average score of 30 correct?
So if we wanted the top 3 scores averaged we merely change the 2 > to 3 > in the code above?
That sounds good to me.
18,33,14,27,21,24
and lets say the query checks those rows as X.score in the order they appear here. I know it could be random which row is checked, but hey, it's my scenario.
so X.score=18 is checked and there are four rows with scores greater than it, so it gets discarded.
it would then move on to X.score=33 and find out there are no scores above that so that would be one of the rows that we would use.
It would then step through X.score=14 and discard it for the same reason as X.score=18.
It then steps through to X.score=27 and finds there is only one score greater than it so it must be the second highest score.
So then the scores are 33 and 27 and we now take the average of those two scores to find that Dave has an average score of 30 correct?
So if we wanted the top 3 scores averaged we merely change the 2 > to 3 > in the code above?
That sounds good to me.
right so it takes the highest requested values including ties for the lowest value. so for that data it would average 33, 27,27.
Between this and the top X rows you've shown me recently I think I have a better understanding of these types of queries. appreciated as always.
Between this and the top X rows you've shown me recently I think I have a better understanding of these types of queries. appreciated as always.
- Thread starter
- #9
TonyGroves
Programmer
Or, you could just split Rudy's code into 2 queries:
[tt]
SELECT @c:=count(*)
FROM game_details
WHERE player_id = X.player_id AND score > X.score;
SELECT player_id, avg(score) as average_score
FROM game_details as X
WHERE 2 > @c
GROUP BY player_id;
[/tt]
Of course, this is MySQL-specific code.
[tt]
SELECT @c:=count(*)
FROM game_details
WHERE player_id = X.player_id AND score > X.score;
SELECT player_id, avg(score) as average_score
FROM game_details as X
WHERE 2 > @c
GROUP BY player_id;
[/tt]
Of course, this is MySQL-specific code.
- Thread starter
- #16
- Status