How to make a SQL query "you are better than XX percent of other users" for MSSQL2000

Question

I have a large table with a list of such points, one rating for each user:

user,score
------
 matt,10
 john,20 
 bill,30

I want to make a request that answers the question: "John, are you faster than xx percent of others"

How to make such a query for Microsoft SQLServer 2000 with optimal performance.

Answer 1

I do not have MSSQL 2k, but it works in 2005 and with the proper indices in both points, and the user should work quite well.

SELECT (count(*)/(SELECT cast(count(*) as float) FROM users))*100 FROM users
WHERE score < (SELECT score FROM users WHERE user = 'john')