PHP/MYSQL Query to block duplicate entries

[php2010]

The only 2 tables relevant to what I want to do are "User" and "Scores." Basically this database holds high scores for a project I've been working on. I only want to display 10 scores, all from different users. No 2 or more scores from the same user. This is my db structure within phpmyadmin:

 

database -> table -> X / User / Score / X / X / X / X / X / X / X / X

 

If possible I'd like to run a query where it checks for duplicate entries by the same user and only show the highest score. The current query I'm running is below:

 

$result = mysql_query("SELECT * FROM table ORDER BY Score DESC LIMIT 0, 10");

 

Is this possible? I can only seem to find information regarding the INSERT IGNORE clause and that obviously won't help me as the users and scores are already in the database. Thanks.

[btherl]

If you want user and their highest score only, you can do this

 

SELECt user,max(score)
FROM  table
GROUP BY user
ORDER BY max(score) DESC

[objnoob]

You want to display the TOP 10 Scores of distinct Users?

  SELECt user,max(score) as score 
  FROM  table
  GROUP BY user
  ORDER BY score DESC 
  LIMIT 10

[php2010]

I don't know if I explained it correctly. Will the above queries work like this?

 

This is what I mean:

 

user1 | score 1

user2 | score 2

user3 | score 3

user4 | score 4

user5 | score 5

user6 | score 6

user7 | score 7

user8 | score 8

user9 | score 9

user10 | score 10

 

Basically a top ten that excludes multiple scores from the same user. Say user1 submitted 5 scores, I don't want to display all 5 I JUST want to display the highest score from that user and then go on to whoever is second place and so on. Is that possible?

 

Sorry if I explained it wrong I just want to make sure we understand each other before I go tinkering with things. Thanks guys!

 

 

Edit: Also I would like to mention that I do need the other sections in the table. Like the 'X / User / Score / X / X / X / X / X / X / X / X' All those X's represent other data that I also need that's echoed into an html table.

[btherl]

Yes it is possible, and that query will do it.

 

If you need the other data items as well then things can get tricky.  It it possible for the same user to have two entries with the same score?  The answer to that determines how the query will look.  It's simpler if they cannot have two entries with the same score, as then (user_id, score) is a unique identifier for the row you want to display.  Otherwise you'll need a way to choose between two equally high scoring rows.

[php2010]

There's no reason why anyone would enter the same score twice via the submit form and even if they do, I would go in and delete it manually if I had to. I will try that query you posted in a little bit. I'm in the process of finishing up some stuff for work right now and don't have the time to do it right at this moment. Maybe in an hour or so.

 

I will report back how it works after I enter some fake scores to test it later. Thanks.

[php2010]

That works. Basically the only thing I needed to change was GROUP BY user. Thanks a ton.