I'm looking for a solution and I believe sub-quering is the answer as I have slight knowledge of how they work. I'm running the following query:
Code: Select all
SET @pos=0;
SELECT @pos:=@pos+1 AS pos, mid, score
FROM games_scores
WHERE gid = {$r['gid']}
ORDER BY score DESC
What this does is return, in score descending order and with a position column next to each, a score board. Because there can be over 1000 rows returned I don't want to have to put all those rows into a PHP array just so I can fetch out 1 row from it. I was hoping I could run the above query as a sub-query for another query which only fetches the row where the user ID is equal to a specified value.
Hopefully somebody can confirm whether this is possible with sub-queries or suggest a more efficient way of doing this.
Thanks,