0

I'm working on a time based game (where a lower time is better), and I have a leaderboard for it, which stores every playthrough into a SQL database.

I'd like to get the top 15 lowest (best) times from the database, but only show one output for each user, so that the best times of the top 15 users are displayed, but I can't seem to do it while returning all of the information.

The Query I'm using is:

SELECT * FROM `scores` WHERE size='$size' ORDER BY `time` ASC LIMIT 15

Thank you.

Weston Reed
  • 187
  • 2
  • 9

3 Answers3

1
SELECT * FROM (SELECT user,size,min(time) as time FROM scores
WHERE size = '10x10' 
GROUP BY user, size)
ORDER BY time
LIMIT 15

Selects minimum time for each users and returns top 15 users with their min time score.

Vahid
  • 1,829
  • 1
  • 20
  • 35
1

If you group your data using the user column, you can use MIN() to isolate the lowest/best time for each users. Finally, you sort by BestTime ASC (so that lower numbers are listed first) and truncate the result set with LIMIT.

Query:

SELECT `user`, MIN(`time`) AS BestTime 
FROM `scores`
WHERE `size` = '10x10'
GROUP BY `user`
ORDER BY `BestTime`
LIMIT 15;
mickmackusa
  • 43,625
  • 12
  • 83
  • 136
0

You would appear to want something like this:

select s.*
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.userid = s.userid)
order by s.score asc
limit 15;

I have no idea what size is for in your sample query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786