SQL Question: Getting the rank

Hi,

I've got a table called Persons, who contain persons. Each person has a
value and I wish to get a "persons rank".

I need to print the result in a browser btw. (not that it matters..)

I tried the following:

my idea was, that I would get the sorted posts, and then simplt iterate
through the posts. Ofcause, I could return the sorted result set, and
then iterate through the set myself, but I guess it would be slower/
more resource demanding.

Anyone got any pointers?

Thanks in advance,

/Svend Rost

Can you explain your table a bit more? Your SQL seem to be assuming multiple rows for single users, rather then a single row. Which is it?

Thanks for the reply.

My tubles look like this: Person(id, name, value).

The table isn't sorted, and after I update a persons value I'd
like to get his/her rank.

/Svend Rost

hmm..

the above seems to work - but it doesn't return what I need. It returns 1
(I guess that's because there's only one tuble with id=2).

Any got a pointer?

Edit: Now that I think about it.. how about JOIN.. would it not be possible
to do something like select count(*) from player as p1, player as p2 where ?
Edit 2: Im trying something like:
SELECT COUNT(*) FROM player p1, player p2 WHERE p1.id=10 AND p1.score>p2.score;
at the moment, but I get a "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." error when I try to
run the query.

[ May 09, 2005: Message edited by: Svend Rost ]
[ May 09, 2005: Message edited by: Svend Rost ]

Problem solved.

I realised, that when I wanted to update a score, I (ofcause!) had to
transfer the value.. LOL

anyways, the final SLQ query was:

SELECT COUNT(*) FROM player WHERE "+Request.Querystring("score")+ "<player.score;

Please note, that you have to add 1 to the result you get from the above
query, to get the correct rank.

/Svend Rost