I'm just wondering if there is a more efficient way of doing this.
I have a table in which I track users scores for different flash games. The table games_scores is defined like this:
Each user can have multiple entries for any game in this table. When I display the score board I only select the MAX score fore every user. Nothing fancy really.
What I'm doing then is displaying the position of the loged-in user if he is not present on the score board (the board only shows top 20 players). I've done this with two queries. The first one tells me the position of the user:
SELECT
COUNT(*) + 1
FROM (
SELECT
MAX(score)
FROM
games_scores gs
WHERE
gs.games_id = ?
AND gs.score > (
SELECT
MAX(gs2.score)
FROM
games_scores gs2
WHERE
gs2.games_id = ?
AND gs2.users_id = ?
AND gs2.add_time = ?
)
AND gs.add_time = ?
GROUP BY
gs.users_id
) AS tmp_table
And the second gives me his score:
SELECT
MAX(gs.score)
FROM
games_scores gs
WHERE
gs.games_id = ?
AND gs.users_id = ?
gs.add_time >= ?
I'm wondering if there is a more efficient way of doing this? Combining the queries into one for instance.