Best DB setup for best daily/weekly/ever high scores

Hey everyone!

Right I want to store peoples high scores in the games on my site and be able to easily recall a high score list showing:

The best scores today

The best scores this week

The best scores ever

When I show the lists though I don't want it to show duplicated scores by the same user, only their best one

ie.

1. Gaz - 100 points
2. Jenny - 80 points
3. Bob - 70 points

instead of

1. Gaz - 100 points
2. Gaz - 95 points
3. Jenny - 80 points

Now, bearing in mind I expect to have xx,xxx users saving their scores, what's the most efficient way to do it?

1. Have 3 tables (best ever, weekly and today) + have a cron empty the weekly and today ones when appropriate.

2. Store all the scores every player ever gets then use sql statements to sort them out?

 

You can use

"SELECT user, score FROM table GROUP BY user ORDER BY score DESC"

and you will only get one result per user.

As far as the structure, I would do it in a single table with a date column, and then select within a certain date range. This would work fine with the above suggested query, you just need to figure out the syntax for selecting only from the past week / month / year etc.

 

You should be using

SELECT user, MAX(score) from.....

 

ok this has kinda worked, the final query I'm using looks like this:

SELECT name,mid,comment,datescored,MAX(score) AS score FROM ibf_games_scores WHERE gid=33 AND (datescored > 1207691236) GROUP BY mid ORDER BY score DESC,timespent ASC LIMIT 0, 10

Code (markup):

There's a slight problem though I think. It retrieved the max score fine but the comment and datescored don't always correspond to the same row which had the max score on it :S

Anyone know how to make it so they do?