Sorting problem. Need to know sorting order of a record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
LonelyProgrammer
Forum Contributor
Posts: 108
Joined: Sun Oct 12, 2003 7:10 am

Sorting problem. Need to know sorting order of a record

Post 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!
MaNiAC
Forum Newbie
Posts: 20
Joined: Fri Dec 23, 2005 4:20 am

Post 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
LonelyProgrammer
Forum Contributor
Posts: 108
Joined: Sun Oct 12, 2003 7:10 am

Post 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...
Post Reply