I want to select one row out of a very long list, and I want a numerical rank based on the column 'points'

i want to:
select name from players_attack where name='$name'

now how would i figure out where in the rankings?

The general idea would be something like this:

"select name, points from players_attack order by points desc"


will order the list by their points in descending order. You need to know where they are in the ranking list, which means you need to return more than one row.

Next, find out what row they are in.


`mysql_row_tell()'
------------------

`MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)'

I've not used this - give it a try.

offset = mysql_row_tell($result)

PS, if you have a big database it might be tempting to put a limit clause on your query to avoid returning the whole data set, but if I remember correctly that will mess up ORDER BY.

Test, test and test again...

thanks, ill work on that. one more thing...if i have four numbers, and i want to select the highest of the four is there an easy way to do that? or will i have to compare the numbers individually?

I just realized that I gave you half or a quarter of an answer. Once you have your ordered list, you still need to find the position of "name" within that list.

You could do that via a procedural language (like PHP etc), or you might consider creating a temp table to store the result and then query that.

i.e.

create table tmp select name, points from players_attack order by points desc

and then

select name, points from tmp where name = '$username'

and then test which row num that is in.

HOWEVER... that doesn't help you with dealing with usernames that have the same rank as another (ties).

Maybe you don't care about that but you might if you had 1000 users all with the same rank of 0 , the scheme discussed will rank them differently even though they are effectively the same rank.

The alternative would be to create a temp table of ranks based on unique scores. To combat this you need to create a temp table that is composed only of ordered, distinct, scores.


from players_attack
mike 5
tim 0
fred 0
sue 1
mary 5
bob 0


If you order that the people that have 0 will end up in last, next to last, next to next to last rank. Clearly that is wrong. And mike or mary will be first or second even though they are tied.

alternatively:

create table tmp select distinct points from players_attack order by points desc

Gives you
5
1
0

Which you can then test for the row_num to determine the RANK of the point value.

Then its trivial to map RANK on to the users actual score.

Some assembly required.

PS, re 4 different point value columns - that's bad design and cumbersome to develop a query around - quickly thinking here I suspect you'll need to develop some complex case statements and I can't remember if MySQL can even handle that. It would be better to have a table of players_attack_results with points as rows per player. Either way, some work to do. I would start on the simple case first.

PSPS: Depending on how large your players tables are you may have to do some extra thinking here. Creating a tmp table on the fly is ok for a few hundred rows but not for a few hundred thousand - particularly if you are doing this on every page load. In that case you might be better off adding a player ranking column to your players table and rebuild it every once in a while.

thanks for the help! ill work on this and let you know what i make of it. the reason i wanted best of four was because there are 4 different categories: goalie, attack, defense, middie. my query selects the top of each category, but i want to also find the top overall. actually, i think i will restructure it and avoid that