How can I select highest score the correct way in MYSQL?

I'm trying to accomplish:

• selecting top 20 highest score users with the least SQL / PHP way possible.
• caching is still not been considered for now.

What I've done so far:

I'm able to retrieve all 5k+ records with their scores, but not able to limit to only retrieve or calculate top 20(example).

Tables:

users (id, name) score_rec (id, uid, points) This table has multiple entries for each user. Highest scores will be the ones which has highest amounts of rows, entries. Example: UID 23 could have 5 rows which belong to it, his score is 5.

Code Sample:

$query = "SELECT * FROM score_rec,users wher开发者_StackOverflowe users.id = score_rec.uid";
    $result = mysql_query($query);

    $array1 = Array();

    while($row = mysql_fetch_array($result) )
        {
            //Count their scores
            $query2 = "SELECT users.id,users.name,score_rec.uid FROM `score_rec`,`users` where score_rec.uid = $row[uid] and users.id = $row[uid]";
            $result2 = mysql_query($query2);

            $scores_count = mysql_num_rows($result2);                

            $array1["$row[name]"] = $scores_count;      

        }

I'm thinking this might be possible with maybe a temporary table script, stored procedure, or simple query which could look at both tables. Since scores_rec could be used by itself to calculate higuest entries holders, maybe one query could suffice to both tables.

Thank you all for any direction given.

---

What about something like this :

select users.id, count(*) as score
from users
    inner join score_rec on users.id = score_rec.uid
group by users.id
order by count(*) desc
limit 20

This will :

• For each user, count how many rows he has (because of the group by)
• sort all users by number of rows -- in descending order
• keep the first 20 resulting rows -- which are the 20 users who have the bigger number of rows

---

$query = "
    SELECT users.id,users.name,count(*) score
    FROM score_rec
    INNER JOIN users on users.id = score_rec.uid
    GROUP BY users.id,users.name
    ORDER BY score DESC
    LIMIT 20
    ";
$result = mysql_query($query);
$array1 = Array();

while($row = mysql_fetch_array($result) )
    {
        $array1["$row[name]"] = $row['score'];
    }

---

Depending on how you want your scores:

1) global top 20 scores, possibly repeating users:

SELECT users.id, users.name, score_rec.points
FROM users
LEFT JOIN score_rec ON users.id = score_rec.uid
ORDER BY score_rec.points DESC;

2) scores of the top 20 distinct players:

SELECT DISTINCT users.id, ...
etc...