highest score of each user

[delpino]

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?

[dragonhawk]

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];

[redemption]

i'm thinking this?:

SELECT user, MAX(score) FROM your_table GROUP BY user;

but i think you tried this right delpino?

[delpino]

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.

[MattR]

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.

[delpino]

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

[MattR]

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?