CODEWITHSUNDEEP
sqlsql-serverstored-procedures
parek
parek

Reputation: 782

SQL Server row number issue

How do I get the row number of a specific value in the table? This only result me the row number 1 and I know that's because I'm only returning 1 row but how am I supposed to do this?

I want to know the "rank" of the specific user comparing to the whole table so I want the return data to be like: 

Rank    UserId
 326    c7f3b047-b5ee-4619-a409-8ff7b29179f8

Query:

DECLARE @UserId uniqueidentifier
SET @UserId='c7f3b047-b5ee-4619-a409-8ff7b29179f8'

SELECT 
    ROW_NUMBER() OVER (ORDER BY Ratio desc) AS 'Rank', 
    ROW_NUMBER() OVER (ORDER BY RatioMonth desc) AS 'RankMonth', 
    ROW_NUMBER() OVER (ORDER BY RatioWeek desc) AS 'RankWeek', 
    UserMessData.UserId, 
    aspnet_Users.UserName, 
    Wins, Losses, Ratio, 
    WinsMonth, LossesMonth, RatioMonth, 
    WinsWeek, LossesWeek, RatioWeek 
FROM 
    UserMessData 
JOIN 
    aspnet_Users ON aspnet_Users.UserId = UserMessData.UserId
WHERE 
    UserMessData.UserId IN (@UserId)
ORDER BY 
    'Rank' ASC

Some help please!

Upvotes: 1

Views: 205

Answers (1)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

You will have to find user's position within the whole list, and then filter:

DECLARE @UserId uniqueidentifier
SET @UserId='c7f3b047-b5ee-4619-a409-8ff7b29179f8'

select *
from
(
    SELECT ROW_NUMBER() OVER (ORDER BY Ratio desc) AS 'Rank', 
    ROW_NUMBER() OVER (ORDER BY RatioMonth desc) AS 'RankMonth', 
    ROW_NUMBER() OVER (ORDER BY RatioWeek desc) AS 'RankWeek', 
    UserMessData.UserId, 
    aspnet_Users.UserName, 
    Wins, Losses, 
    Ratio, 
    WinsMonth, 
    LossesMonth, 
    RatioMonth, 
    WinsWeek, 
    LossesWeek, 
    RatioWeek FROM UserMessData 
    JOIN aspnet_Users ON aspnet_Users.UserId=UserMessData.UserId
) a
WHERE a.UserId IN (@UserId)

Upvotes: 5

Related Questions