Join these two queries in one

Question

I'm just wondering if there is a more efficient way to do this.

I have a table in which I track user ratings for different flash games. The games_scores table games_scores defined as follows:

alt text http://img13.imageshack.us/img13/7262/structure1.png

Each user can have several entries for any game in this table. When I show the score card, I only select the MAX metric for each user. Nothing special.

What I do shows the login position if it is not present on the scoreboard (only 20 players are displayed on the board). I did this with two requests. The first tells me about the user's position:

 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 assessment:

 SELECT MAX(gs.score) FROM games_scores gs WHERE gs.games_id = ? AND gs.users_id = ? gs.add_time >= ? 

I am wondering if there is a more efficient way to do this? Combining queries into one, for example.

Answer 1

What you are looking for is a group max: http://jan.kneschke.de/projects/mysql/groupwise-max/