Page 1 of 1

Sorting problem. Need to know sorting order of a record

Posted: Fri Dec 23, 2005 1:17 am
by LonelyProgrammer
Hi all,

I am developing a mini-game, in which the results will be stored in a database. I need to display the player's score, showing him where he ranks. For instance, if the player is ranked as the 10th position, then I will need to show the score for the 5th position to 15th position.

I could simply use ORDER BY and LIMIT to get the results I need, maybe something like this:

SELECT player_name, score FROM highscore WHERE score < $player_score ORDER BY score DESC LIMIT 0, 5;

and

SELECT player_name, score FROM highscore WHERE score > $player_score ORDER BY score DESC LIMIT 0, 4;

The problem is I have no idea what will be the position of the player. I could get the entire result-sets and do a sort, thus knowing which position of the player, but it strikes me as ineffective. Is there a better way?

Thanks in advance!

Posted: Fri Dec 23, 2005 4:56 am
by MaNiAC
I think it's possible using subqueries.. I'm gonna look into it for you ;)

EDIT:
I tried it... MySQL gives me the error... it says that this function is not yet supported. I'm afraid you'll have to wait for a newer function of MySQL :P

Posted: Fri Dec 23, 2005 11:17 am
by LonelyProgrammer
I'm not sure if this would work...

Get the player id
SELECT MAX(id) FROM highscore

Then count the number of records
SELECT COUNT(id) FROM highscore WHERE score <= $player_score AND id <> $player_id

It's way past bedtime for me now, so I couldn't test it...