mySQL query, for discrete results limited and ordered by a joined column

Question

I have two tables

User
  id
  name

Result
  id
  user_id
  score
  created_at

Every hour a cron job runs and puts data in the result table for each user - assigning them a score based on some external variable parameters.

So the Result table contains many records for each user.

I want to retreive a 'top ten' users at any given point. So only one row for each user should be returned, and it should contain their most recent available score, and ideally only 10 rows relating to those ten users with the highest scores.

I currently fetch all results and do the leg work with php which I'm fairly sure would be faster and more efficient if handled by the database. But I don't know where to start.

Answer 1

This should do the job:

SELECT user.id, user.name, MAX(result.score) maxscore FROM user
Left Join result on result.user_id=user.id
GROUP By user.id
ORDER By maxscore DESC
LIMIT 0,10

---

Second try not sure about it yet...

SELECT user.id, user.name, result.score, result.created_at FROM user
LEFT JOIN result ON result.user_id = user.id
WHERE result.id IN (SELECT r.id FROM result r GROUP By r.user_id ORDER By MAX(r.created_at))
ORDER By score DESC
LIMIT 0,10

Answer 2

This may work:

select u.id, u.name, x.score from user u, (select user_id, sum(score) score from results) x where u.id = x.user_id order by x.score limit 10

Answer 3

Ok get the last created date for each user and from there choose the distinct user with their score sorted in descendant order and take the 10 first line

SELECT DISTINCT u.id, u.name, r.score, md.max_date
FROM user u
INNER JOIN result r ON (u.id=r.user_id)
INNER JOIN (
 SELECT user_id, MAX(created_at) max_date
 FROM result
 GROUP BY user_id
) md ON (md.user_id=r.user_id AND r.created_at=md.max_date)
ORDER BY r.score DESC
LIMIT 10

Answer 4

SELECT u.id,
       u.name,
       r.score
FROM Result r
JOIN
  (SELECT user_id, MAX(created_at) AS max_date
   FROM Result
   GROUP BY user_id) lr ON r.user_id=lr.user_id
JOIN User u ON u.id=r.user_id
WHERE r.created_at=lr.max_date
ORDER BY r.score DESC LIMIT 10;

A sample run:

mysql> insert into User (name) values ('foo'), ('bar');
mysql> insert into Result (user_id, score, created_at) values (1,100,'2010-01-20'), (2,150,'2010-01-20'),(1,150,'2010-01-21'),(2,100,'2010-01-21');
mysql> SELECT u.id,        u.name,        r.score FROM Result r JOIN   (SELECT user_id, MAX(created_at) AS max_date    FROM Result    GROUP BY user_id) lr ON r.user_id=lr.user_id JOIN User u ON u.id=r.user_id WHERE r.created_at=lr.max_date ORDER BY r.score DESC LIMIT 10;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | foo  |   150 | 
|  2 | bar  |   100 | 
+----+------+-------+
2 rows in set (0.00 sec)