I know how to obtain the max score fro each player, as shown below, but what I realy want is the top 5 results and the total as shown in the second table. Help with the SQL to do this would be greatly appreciated.
Code:
SELECT player, MAX(score) as score1
FROM results
GROUP BY player
player score1 score2 score3 score4 score5 total
alan-----99-----0-----0-----0-----0-----99
colin-----50-----50-----------------------100
bill-------9-----6------2-----1-----------18
I would assume colin is then first?
But what if alan had a sixth score, of 2, which (if you added all his scores) would give him 101?
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Probably the best way to do it would be to write a Stored Procedure, create a temporary table in the SP, and go from there.
We can simulate that with a series of SQL queries, say from PHP code (or ASP or JSP or whatever). But if you understand stored procedures, just put all this into one.
Ugh... Except I just tested this code, and you can NOT use a temporary table for one of these steps. (Another incredibly stupid limitation of MySQL!) So you'll have to use an ordinary table.
If you think more than one person might hit your site at the same time to see these results, you might want to use PHP to create guaranteed unique table names per user.
Or, if you only need to get the (non temp) table once per day, you could do that. And then just use the last SELECT query to show results in the web page.
Code:
CREATE table tempResults(
player VARCHAR( 10 ) ,
score_id INT );
INSERT INTO tempResults (player,score_id)
SELECT player, MAX(score * 1000000 + result_id ) FROM results GROUP BY player;
/* REPEAT this next step for a TOTAL of FOUR times! Really! */
INSERT INTO tempResults (player,score_id)
SELECT results.player, MAX(score * 1000000 + result_id )
FROM results LEFT JOIN tempResults
ON results.player = tempResults.player
AND score * 1000000 + result_id = score_id
WHERE tempResults.player IS NULL
GROUP BY results.player;
/* then this gets the final results */
SELECT player,
GROUP_CONCAT( FLOOR(score_id/1000000) ) as scores,
sum( FLOOR(score_id/1000000 )) AS total
FROM tempResults
GROUP BY player
ORDER BY total DESC;
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Last edited by Old Pedant; 06-01-2012 at 07:38 PM..
mysql> select * from results;
+-----------+--------+-------+
| result_id | player | score |
+-----------+--------+-------+
| 1 | ann | 10 |
| 2 | ann | 20 |
| 3 | ann | 30 |
| 4 | ann | 40 |
| 5 | ann | 50 |
| 6 | ann | 50 |
| 7 | bob | 80 |
| 8 | bob | 95 |
+-----------+--------+-------+
8 rows in set (0.00 sec)
Notice that ann has SIX results! And notice that her top two scores are the same. Handling more than 5 results and handling same scores for one person was the tricky part of all this.
Here is what the tempResults table looks like after creating it and then after the FIVE queries that were used to load data into it:
Code:
mysql> select * from tempResults;
+--------+----------+
| player | score_id |
+--------+----------+
| ann | 50000006 |
| bob | 95000008 |
| ann | 50000005 |
| bob | 80000007 |
| ann | 40000004 |
| ann | 30000003 |
| ann | 20000002 |
+--------+----------+
7 rows in set (0.00 sec)
The "trick" I used here was to multiply the scores by 1,000,000 and then add on the ids. Notice for ann that this means that her top two scores are now, effectively, not the same, thanks to using the ids are the tiebreakers.
Finally, here are the results of that final SELECT query:
Code:
mysql> SELECT player,
-> GROUP_CONCAT( FLOOR(score_id/1000000) ) as scores,
-> SUM( FLOOR(score_id/1000000 )) AS total
-> FROM tempResults
-> GROUP BY player
-> ORDER BY total DESC;
+--------+----------------+-------+
| player | scores | total |
+--------+----------------+-------+
| ann | 50,50,40,30,20 | 190 |
| bob | 95,80 | 175 |
+--------+----------------+-------+
2 rows in set (0.02 sec)
See how I stripped the id's off of the scores? By dividing by 1,000,000 and then using FLOOR to zap all the decimal places.
Notice that the scores come out in a single field in a list format. I figure you can then easily use PHP/ASP/JSP to split apart that list and put the individual scores into columns.
It would have been really really tough to get them into individual fields, so I hope this is okay.
Will that work for you? It assumes you won't have any result_id values greater than 999,999. I would hope that is true.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Last edited by Old Pedant; 06-01-2012 at 12:13 AM..
Thank you for taking the time to help me with this.
I think its fare to say I’m still a bit wet behind the ears as far as SQL is concerned and I’m struggling to understand the above code.
When I run it in phpMyAdmin I get the following results;-
Code:
player | scores | total
ann |[BLOB - 17B] | 200
bob |[BLOB - 5B] | 175
I understand the workaround to ensure each result is unique. As for the rest, my head hurts, if its ok, I’d like to come back when I have some sensible questions.
Thanks
Chris
That's just phpmyadmin being stupid...not unusual.
Indeed the scores column *does* come back as a BLOB, because of the use of GROUP_CONCAT, but it really is usable as just a string/text if you treat it as such in PHP.
Notice the 17B and 5B? That is 17 BYTES and 5 BYTES. And, indeed, that's the right number of bytes. (Example "95,80" is 5 characters, total, and so 5 bytes.)
We might be able to convince even silly phpmyadmin to do the right thing.
Try this as the final step:
Code:
SELECT player,
CAST( GROUP_CONCAT( FLOOR(score_id/1000000) ) AS CHAR ) AS scores,
sum( FLOOR(score_id/1000000 )) AS total
FROM tempResults
GROUP BY player
ORDER BY total DESC;
That *should* force phpmyadmin to accept that scores really is a string.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
I'm not sure, since you got the BLOB results, but I *think* you did that "repeat 4 times" step 5 times, instead of just 4. I meant for you to do it a *TOTAL* of 4 times. Not do it once and then repeat 4 times.
I went back and changed the comment (but not the code).
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Sorry to be a pest but I’ve realised as I’m working on this I need to bring another piece of data – “filename” with each row.
I think I’m ok with the insert into tempResults below. My problem, I think, is with the final select queery; ive tried repeating the group concat statement but this just produces an error, please can you advise.
Many thanks
Chris
Code:
INSERT INTO tempResults (player, fileName,score_id)
SELECT player, MAX(score * 1000000 + result_id ) FROM results GROUP BY player;
/* done this 4 times */
INSERT INTO tempResults (player, fileName,score_id)
SELECT results.player, MAX(score * 1000000 + result_id )
FROM results LEFT JOIN tempResults
ON results.player = tempResults.player
AND score * 1000000 + result_id = score_id
WHERE tempResults.player IS NULL
GROUP BY results.player;
I've added the colum, "fileName" to the tempResults but his seems to be where my probem is
Code:
SELECT player,
CAST( GROUP_CONCAT( FLOOR(maxDist_id/1000000) ) AS CHAR ) AS best5,
CAST( GROUP_CONCAT(fileName) ) AS CHAR) AS fileName,
sum( FLOOR(score_id/1000000 )) AS total
FROM tempResults
GROUP BY player
ORDER BY total DESC;
Last edited by chrisfozz; 06-06-2012 at 07:07 PM..
Reason: not thinking ahead
Before you post, read our: 
