Hi
I have a table with:
player_name, top_score, number_of_plays

When I list them out I "ORDER BY top_score DESC, number_of_plays DESC,
player_name" to give some sort of order to the people with the same
scores.

What I would like to do is find out a players position without looping
through all the records, so my plan was to do a "SELECT count(*)" and
have "WHERE top_score > " the players top score.. however when there are
many scores the same I want to also do "WHERE number_of_plays > " the
players number of plays.

Doing "WHERE top_score > 1000 AND number_of_plays > 10" is no good as
some players have higher scores but lower plays but should be counted as
been higher ranked.

I don't want to loop through the scores, that's not very elegant. Also
creating a temp table where the scores are in order and then counting on
that would also be overkill?

I hope this makes sense and that there is a solution.
--
David Scott

Hi,

Critters wrote:
> Hi
> I have a table with:
> player_name, top_score, number_of_plays
>
> When I list them out I "ORDER BY top_score DESC, number_of_plays DESC,
> player_name" to give some sort of order to the people with the same
> scores.
>
> What I would like to do is find out a players position without looping
> through all the records, so my plan was to do a "SELECT count(*)" and
> have "WHERE top_score > " the players top score.. however when there are
> many scores the same I want to also do "WHERE number_of_plays > " the
> players number of plays.
>
> Doing "WHERE top_score > 1000 AND number_of_plays > 10" is no good as
> some players have higher scores but lower plays but should be counted as
> been higher ranked.
>
> I don't want to loop through the scores, that's not very elegant. Also
> creating a temp table where the scores are in order and then counting on
> that would also be overkill?
>
> I hope this makes sense and that there is a solution.

This is a common problem with ranked data. It seems to be exactly the
topic I wrote an O'Reilly article on:

http://www.oreillynet.com/pub/a/mysq...rank-data.html

Baron