This is something called cross table. An excellent example can be found in

http://www.sqlservercentral.com/Forums/Topic242919-8-1.aspx

Taking one step futher on the query you give, I get what I want

SELECT S3.ID, S3.Score

FROM

(SELECT

S1.ID,

S1.Score,

1 + (SELECT COUNT(DISTINCT(S2.Score))

FROM #Scores S2

WHERE S2.Score<S1.Score

AND S1.ID=S2.ID) AS 'Rank'

FROM #Scores S1

GROUP BY...

Thanks for your response.

The results from searching "top nth" seem to deal with a query that find the nth row for whole table. What I want is to find the...