Jump to content

Getting The Three Highest Scores Of Each Player And Then Putting Them In Average Order


Recommended Posts

Hi,

 

I am writing code to find the average of each players highest 3 scores (and they must have 3 scores minimum),

So if the database says

James 50

Emma 30

James 60

Emma 40

Darren 45

James 40

Darren 55

James 25

Darren 35

 

Then I would like to find the highest averages of the top three scores; so James would be 50+60+40 (only highest 3 scores) = 50 and Darren would be 45+55+35=45 while Emma would not be included because she only has two scores.

 

James

You may need to combine that with joining to the table on itself. Is there a primary key?

Can you post the table structure please? (Edit: and a dump of data)

 

And you want the actual scores or just the sum/average of them?

Edited by Jessica

Table name : sleuth_game

Columns : playerID, puzzleID (primary key), puzzle_level, total_time, final_score

Data:

101, 1, 4, 123, 50

102, 2, 4, 215, 30

101, 3, 4, 198, 60

102, 4, 4, 186, 40

103, 5, 4, 196, 45

101, 6, 4, 187, 40

103, 7, 4, 164, 55

101, 8, 4, 258, 25

103, 9, 4, 177, 35

 

and I want the AVERAGE of the HIGHEST 3 scores for each person(playerID).

 

So playedID of 101 would have the three highest scores of 50, 60 & 40 (even though they had 4 scores only their top three count) and the average would be 50.

playerID 102 only has 2 scores so they are not included.

playerID has 45, 55 & 35 so their average would be 45.

 

So the expected output would be :

101, 50

103, 45

Try with sub-query,

SELECT playerID, AVG(final_score) FROM `sleuth_game` WHERE `sleuth_game`.`playerID` IN(
SELECT playerID
FROM `sleuth_game`
GROUP BY playerID
HAVING COUNT( playerID ) > 2) GROUP BY playerID

Not sure if this is even possible because there's no real way for MySQL to say "show me every row which matches col=val, ordered by col2, but only the first 3 for every distinct value in col1." You may want to just pull all the scores for any player with more than 3 scores, sorted by score DESC, and loop through them in PHP, skipping any unnecessary rows.

 

That seems wasteful, but i can't see a purely SQL solution to your problem.

I tested it. It works, but him mysql structure is not very well at all..

Result is:

 

+----------+------------------+

| playerID | AVG(final_score) |

+----------+------------------+

| 101 | 43.7500 |

| 103 | 45.0000 |

+----------+------------------+

 

P.S Hm....now I saw that he wants to count only first three results from playerID = 101, but I count all of them.

That's why my average is 43.7500.

Edited by jazzman1

@cliftonbazaar, you have to create another sub-query after AVG(SELECT........) to count and limit the number of final_score to 4 order by puzzleID DESC, I think..

Edited by jazzman1

This may (or may not) help you. I thought I should at least show it to you

http://rickosborne.o...-grouped-query/

 

Here is another source that should help you

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Edited by Zane

Thanks for all the replies, unfortunately I have to go out now :( I will try all the options that were suggested, if nothing works I think I'll have to change things :)

First run a query to initialise user variables

SET @num := 0, @id := 0;

 

Then

SELECT playerID, AVG(final_score) as average
FROM
   (
   SELECT playerID, final_score,
  @num := IF(@id = playerID, @num + 1, 1) as row_number,
  @id := playerID as dummy
   FROM sleuth_game
   ORDER BY playerID, final_score DESC
   ) as x
WHERE x.row_number <= 3
GROUP BY playerId
HAVING COUNT(*) > 2;

 

Results

+----------+---------+
| playerID | average |
+----------+---------+
|	  101 | 50.0000 |
|	  103 | 45.0000 |
+----------+---------+

One more solution, I used the pattern of the link posted by @Zane...

SELECT c.playerID, AVG(c.final_score) as average FROM sleuth_game c
INNER JOIN (
SELECT a.puzzleID FROM sleuth_game a
INNER JOIN sleuth_game b ON (a.playerID = b.playerID) AND (a.final_score <= b.final_score)
GROUP BY a.puzzleID
HAVING COUNT(*) <= 3) AS x ON (c.puzzleID = x.puzzleID)
GROUP BY c.playerID
HAVING COUNT(*) > 2

 

Result:

+-----------+------------+
| playerID | average |
+-----------+------------+
|  101 | 50.0000 |
|  103 | 45.0000 |
+----------+-------------+

Edited by jazzman1
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.