[SOLVED] MySQL rankings with no shared ranks

[xxavalanchexx]

I have a table with thousands of rows, each row representing a player with a certain score. I need to quickly determine a player's ranking compared to the other players, and by searching I was able to find this:

 

http://www.phpfreaks.com/forums/index.php/topic,207780.msg943662.html#msg943662

 

Barand's solution works perfectly, except that in my case each player needs to have a unique rank. e.g. even if the two top players both have a score of 100, they can't both be #1; one of them has to be #2. It wouldn't matter how tied players are differentiated, so long as they are. Any suggestions?

 

Thanks.

[xxavalanchexx]

Bump?

 

I'm thinking that I'll have to do two queries. The first would be the original query in the thread that I linked, in order to get the number of players with more score than the player. Then I would have to do a second query to check for all players with an equal score, and sort through that to determine the final position. Any other ideas?

 

Thanks.

[fenway]

Great question... have you read this?

[aschk]

You state the following:

"...even if the two top players both have a score of 100, they can't both be #1; one of them has to be #2..."

 

You then say:

"...It wouldn't matter how tied players are differentiated..."

 

These 2 statements are contradictary,  because you MUST have a differentiate to pick one of them at #1 and one as #2.

 

And I can only imagine that the players will be looking at this ranking saying "why am I #2 when I have the same score???". So either put in ties, or find another statistic on which to differentiate the rankings.

[xxavalanchexx]

Great question... have you read this?

No, I haven't. I will read through it. Thanks for the link.

 

You state the following:

"...even if the two top players both have a score of 100, they can't both be #1; one of them has to be #2..."

 

You then say:

"...It wouldn't matter how tied players are differentiated..."

 

These 2 statements are contradictary,  because you MUST have a differentiate to pick one of them at #1 and one as #2.

 

And I can only imagine that the players will be looking at this ranking saying "why am I #2 when I have the same score???". So either put in ties, or find another statistic on which to differentiate the rankings.

Yes, there must be a way to differentiate tied ranks. I meant that if two players had a tie score, I wouldn't care how the #1 spot would be chosen, eg: based on alphabetical order of their name, based on the timestamp of when the row was inserted, etcetera. I know that I haven't said anything about the environment that this is in, but it should be enough to say that I can't use tied ranks, sorry.

[xxavalanchexx]

Okay, I conceded; I will have to end up using tied ranks. However, I would like to be able to select the ranking along with other columns in one query. This is what I'm using:

 

SELECT lastname, points AS myPoints,

(SELECT COUNT(*)+1 FROM `gg_stats` WHERE points > myPoints) AS ranking

FROM `gg_stats` LIMIT 10, 10;

 

It does work, but is there a more efficient way to be doing this? Also, is it possible to reference the points column value from the primary query without having to select it AS something else? Sorry, I'm really a MySQL newbie. I'm using MySQL 5.0.27 if it matters.

 

Thank you.

[fenway]

I'm not sure what you mean...

[xxavalanchexx]

Sorry. If I change it to this:

 

SELECT lastname,

(SELECT COUNT(*)+1 FROM `gg_stats` WHERE points > points) AS ranking

FROM `gg_stats` LIMIT 10, 10;

 

It doesn't work correctly. I want to compare the points column of the rows in the subquery with the value of the points column of the rows in the primary query. So, when I select points AS myPoints from the primary query, and then use "points > myPoints", like in my other post, it does work correctly. But is there another way to reference the points column from the primary query without using select AS, like "primary.points" or some other notation?

 

Thanks.

[xxavalanchexx]

Nevermind, I read one of the stickies that had a link to SQLzoo, which had the answer in one of the tutorials.

 

SELECT lastname,

(SELECT COUNT(*)+1 FROM `gg_stats` y WHERE y.points > x.points) AS ranking

FROM `gg_stats` x LIMIT 10, 10;

 

Sorry to waste your time, I should've just read the stickies first!