Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11

Thread: top 5 league

  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts

    top 5 league

    I’m working with MySQL version 5.1.41 and phpMyAdmin.

    I would appreciate some help to create the 2nd table shown below from the following database.

    Code:
    CREATE DATABASE `top 5 league` ;
    
    CREATE TABLE `top 5 league`.`results` ( 
    `result_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `player` VARCHAR( 10 ) NOT NULL ,
    `score` INT NULL 
    ) ENGINE = MYISAM ;
    
    INSERT INTO `top 5 league`.`results` ( 
    `result_ID` ,
    `player` ,
    `score` 
    )
    VALUES 
    (NULL , 'alan', '7'), 
    (NULL , 'bill', '9'), 
    (NULL , 'colin', '6'), 
    (NULL , 'alan', '2'),
    (NULL , 'bill', '6'),
    (NULL , 'colin', '7'), 
    (NULL , 'alan', '12'), 
    (NULL , 'bill', '2'), 
    (NULL , 'colin', '8'),
    (NULL , 'alan', '5'), 
    (NULL , 'bill', '1'),
    (NULL , 'alan', '2'),
    (NULL , 'alan', '3');
    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

    alan ---- 12
    bill ----- 9
    colin --- 8


    player score1 score2 score3 score4 score5 total

    alan-----12-----7-----5-----3-----2-----29
    colin-----8-----7------6-----------------21
    bill-------9-----6------2-----1-----------18


    thanks
    chris
    Last edited by chrisfozz; 05-30-2012 at 06:11 PM. Reason: table not showing very well on posting

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,193
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Ummm...but what about if you had

    Code:
    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.

  • #3
    New Coder
    Join Date
    Jul 2011
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts

    at the minute my glass is empty

    I’d like to order the results based on the total score. Only the top 5 scores for each player to count towards their total.
    Thanks
    Chris

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,193
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    The ordering is easy. Only top 5 per user is tough. Thinking on it...
    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.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,193
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    I think this is a lot tougher than it looks.

    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;
    Last edited by Old Pedant; 06-01-2012 at 07:38 PM.
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,193
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Here's my actual test data and test results.

    First of all, my test data in my results table:
    Code:
    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.
    Last edited by Old Pedant; 06-01-2012 at 12:13 AM.
    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.

  • Users who have thanked Old Pedant for this post:

    chrisfozz (06-01-2012)

  • #7
    New Coder
    Join Date
    Jul 2011
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,193
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,193
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.

  • Users who have thanked Old Pedant for this post:

    chrisfozz (06-02-2012)

  • #10
    New Coder
    Join Date
    Jul 2011
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts
    That works nicely now, I just need to get working with some live data.
    Many thanks for your help
    Chris

  • #11
    New Coder
    Join Date
    Jul 2011
    Posts
    16
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •