Query a Subquery

Question

I have to display the Rank of a user for which I plan to use the Rank() function in SQL Server. The Sql query to get the rank is as follows

select a.user_id, RANK() OVER (ORDER BY (a.quant_points) DESC) AS QRANK,
    RANK() OVER (ORDER BY (a.verbal_points) DESC) AS VRANK,
    RANK() OVER (ORDER BY (a.dilr_points) DESC) AS DRANK,
    RANK() OVER (ORDER BY (a.quant_points+a.verbal_points+a.dilr_points) DESC) AS ORANK
 from users a

However, I only want to get the rank of a particular user. If I add a where clause (where user_id = @user_id) to the above then all the Ranks are shown as 1.

So essentially, I have to query the above resultset treating it as a table. I was thinking of creating a temporary table but felt there might be better ways to achieve this.

Answer 1

How about creating a CTE, and pulling your single user out?

;with  UserRank AS
( 
    SELECT a.user_id, RANK() OVER (ORDER BY (a.quant_points) DESC) AS QRANK,
           RANK() OVER (ORDER BY (a.verbal_points) DESC) AS VRANK,
           RANK() OVER (ORDER BY (a.dilr_points) DESC) AS DRANK,
           RANK() OVER (ORDER BY (a.quant_points+a.verbal_points+a.dilr_points) DESC) AS ORANK
   FROM Users a
 )

 SELECT QRANK, VRANK, DRANK, ORANK, user_id
 FROM UserRank WHERE user_id = @foo