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

[cliftonbazaar]

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

[Jessica]

Select name, sum(scores) from table having count(scores) >2 group by name

 

(I might have the group by / having in the wrong order, on my phone.)

[cliftonbazaar]

Hi Jessica,

 

Thanks for your reply, I nearly have it working from yoru code but I need the three highest scores.

 

James

[Jessica]

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?

[cliftonbazaar]

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

[jazzman1]

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

[ManiacDan]

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.

[jazzman1]

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.

[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..

[Zane]

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/

[cliftonbazaar]

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

[Barand]

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 |
+----------+---------+

[fenway]

You can be extra fancy and initialize those variables as a 'join' to the inner query, too.

[Barand]

I thought so, but I couldn't remember the exact syntax. Played safe.

[jazzman1]

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 |
+----------+-------------+

[cliftonbazaar]

Thanks for all the responses, am going through them now