SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict delpino's Avatar
    Join Date
    Nov 2001
    Location
    London
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    highest score of each user

    I have a table where the scores of a game all of users are saved. Every time a user plays a game the score is saved there. Now I want the highest score of each user.

    I have experimented with MAX() and GROUP BY but it didn't work..

    Any ideas?

  2. #2
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is how I would do it... but the field must be integer.

    $sql="SELECT max(score) as highscore FROM $table";
    $results=mysql_query($sql);
    $row=mysql_fetch_array($results);

    $highscore=$row[highscore];

  3. #3
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm thinking this?:
    Code:
    SELECT user, MAX(score) FROM your_table GROUP BY user;
    but i think you tried this right delpino?

  4. #4
    SitePoint Addict delpino's Avatar
    Join Date
    Nov 2001
    Location
    London
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I tried this, but the problem is that every record has a level and a datetime field too and this is not correct, when I try the following:

    SELECT max(points) as maxpoints, user, level,datetime FROM table group by user order by maxpoints desc

    This sql queries finds the correct maxpoints for each user but then it doesn't get the correct datetime and level, which is from another record, not the one with the highest score.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is expected behavior, since MySQL is allowing you to do something the SQL standard explicitly disallowed -- meaning having columns in the SELECT clause which are not in the GROUP BY and are not aggregates.

    That is to say you are not allowed to add level and datetime per the SQL standard. As you can see, it selects random records, because MySQL has no clue which one to pick. Think about it a little bit more and you'll understand.

  6. #6
    SitePoint Addict delpino's Avatar
    Join Date
    Nov 2001
    Location
    London
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Which means there is no solution to this in mysql? or you do not want to tell me?

    Not 100% sure, but I think this is the way to do it..

    http://www.mysql.com/doc/e/x/example...group-row.html

    so better do it in two steps..
    Last edited by delpino; May 26, 2002 at 13:37.
    http://www.antosch-and-lin.com - Language Learning

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The solution you found is probably something along the lines of a better way. It sounds like your table may be designed incorrectly (or not normalized enough) to handle this sort of query. What is the table schema, what do rows look like,a nd what (in English) are you trying to find?


Bookmarks

Posting Permissions

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