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

Question

I have a large table with list of scores like this, one score per user:

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

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

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

Answer 1

I don't have MSSQL 2k around, but this works on 2005 and with proper indexes in both score and user should perform reasonably well.

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