mysql: query to return scores ranks according to a specific user

Question

Hello. Lets say i have the following scores table (total rows of 10):

rank userid score
---- ------ -----
 1   |455   |10
 2   |435   |9
 3   |354   |8
 4   |342   |7
 5   |343   |6
 6   |123   |5
 7   |125   |4
 8   |128   |3
 9   |5     |2
10   |6     |1

the user can fetch his score status, and he can get 5 score positions around his position. which means:

1. if the user is at 1st place, he will get the results of ranks 1-5
2. if the user is at 6th place, he will get the results of ranks 4-9
3. if the user is at 9th place, he will get the results of rank 6-10

I'm trying to find the most optimized method to perform these operations to get the score status for the user only in SQL, no other language integration.

thanks alot!!!

update

this table is actually created on-the-fly

i have a game_log table with gameid, userid and time and logid (which is playid) and i have game_score table with logid and score.

i use the following query:

@rank:=0;

select @rank:=@rank+1 as rank,userid,score from (
select * from (
   select game_log_gameid as gameid,
   game_log_userid as userid,
(select max(game_score_log_score)
        from game_score_log where game_score_log_logid=game_log_id) as score,
        (select game_score_log_timestamp as time from
            game_score_log where game_score_log_logid=game_log_id and
            game_score_log_score = score order by game_score_log_timestamp desc)
            as time from game_log
where game_log_gameid=620
order by score desc,time
) as a group by gameid,userid ) as b order by score desc,time

Answer 1

I'm not sure it makes a great deal of sense to attempt to do all of this in a single query, as you'd require a sub-select or something similar to obtain the user's rank which you could then calculate the limits around.

As such, it might make more sense to obtain the user's rank with a single query, calculate the limits you want to obtain in your source programming language (handling edge cases will be a lot easier here than in SQL) and then issue a simply query that uses LIMIT to obtain the required data.

For example, once you've computed 'startRank' as above, you'd simply use:

SELECT * FROM table_name WHERE rank >= startRank ORDER BY rank ASC LIMIT 5;

---

Update: This doesn't make quite as much sense now that you've revealed that rank is dynamically calculated, but the general point I was making is still valid - handling edge cases such as initial & final positions will be a lot easier in a programming language than in SQL.