CODEWITHSUNDEEP
sqlsql-serverstored-procedures
RenegadeAndy
RenegadeAndy

Reputation: 5690

SQL Procedure to get row number to determine 'Rank'

I have a table which contains a column called 'Score'. I want to write a procedure which loops through all rows in this table, and determines where out of all rows, each specific row ranks i.e biggest score is rank 1, smallest score = n.

This is my poor attempt so far:

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @Rank INT = 0;
    DECLARE @UserID UNIQUEIDENTIFIER;


    DECLARE cur CURSOR FOR SELECT UserID FROM tblMember
    OPEN cur

    FETCH NEXT FROM cur INTO @UserID
    --loop through all users
    WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @Rank = ROW_NUMBER() OVER(ORDER BY Score DESC) FROM tblDetails WHERE UserID = @UserID;
    PRINT @Rank 
    Print ' For '
    Print @UserID;

    UPDATE tblDetails SET Rank = @Rank WHERE UserID = @UserID; 

    FETCH NEXT FROM cur INTO @UserID
    END
END

Unfortauntely this effort ranks each entry as 1 - probably becuase the query has the WHERE clause refining the result set. But I cannot determine what the correct query should be!

Upvotes: 0

Views: 706

Answers (3)

mohan111
mohan111

Reputation: 8865

may be basing on the userid you are trying to update or increment the rank column..So as per my understanding what ever the Userid = 1 you want to update Rank column 

declare @t INT;
SELECT @t =  MAX(Userid) From tblDetails 
;
    with cte(w) as
(select 1 
        UNION ALL
Select Userid + 1 
                From 
        tblDetails where Userid < @t)
UPdate tblDetails 
set [Rank]=cte.w 
        FROM cte 
INNER JOIN tblDetails  S
ON S.Userid = cte.w

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271013

I believe the correct query you want is:

with toupdate as (
      select d.*,
             row_number() over (order by score desc) as seqnum
      from tblDetails
        )
update toupdate
    set [rank] = seqnum;

I used row_number() because your example does. You might want rank() or dense_rank() if you want scores with the same value to have the same rank:

with toupdate as (
      select d.*,
             rank() over (order by score desc) as seqnum
      from tblDetails
        )
update toupdate
    set [rank] = seqnum;

The important difference is that there is no partition by clause in the query.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425813

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY userId ORDER BY score DESC) rn
        FROM    tblDetail
        )
UPDATE  q
SET     rank = rn

Upvotes: 6

Related Questions