How to figure out what position a record is in the database?

[MrSteve]

Hello

I hope someone can help me with this.

I am working on a website which allows people to vote users up and down.

--- MOD EDIT: External link removed. ---

The way I figure out who is #1 and #2 etc. is simply by sorting the SQL results in desc order.

However I would like to be able to figure out what position each user is on the fly. For example, if the user "lisam03" wanted to know what position she is in, I would like to be able to tell her she is #6. At the moment I cannot do this. I can only tell she is #6 by displaying the results for everyone and finding her position.

Does anyone know any way I can find out, on the fly, what position a record is in a database?

Any help greatly appreciated.

Thanks

[Roger_Ramjet]

Well in the absence of more information, like what column it is that you sort DESC, you can do it this way

SELECT COUNT(user) FROM users
WHERE rating >= (SELECT rating FROM users WHERE user = 'lisam06')

On second thoughts, your rating system allows for equal 5th etc so this needs to change to

SELECT COUNT(user) + 1 AS position FROM users
WHERE rating > (SELECT rating FROM users WHERE user = 'lisam06')