sql server - SQL Procedure to get row number to determine 'Rank' -

sql server - SQL Procedure to get row number to determine 'Rank' -

i have table contains column called 'score'. want write procedure loops through rows in table, , determines out of rows, each specific row ranks i.e biggest score rank 1, smallest score = n.

this poor effort far:

begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; declare @rank int = 0; declare @userid uniqueidentifier; declare cur cursor select userid tblmember open cur fetch next cur @userid --loop through users while @@fetch_status = 0 begin select @rank = row_number() over(order score desc) tbldetails userid = @userid; print @rank print ' ' print @userid; update tbldetails set rank = @rank userid = @userid; fetch next cur @userid end end

unfortauntely effort ranks each entry 1 - becuase query has clause refining result set. cannot determine right query should be!

with q ( select *, row_number() on (partition userid order score desc) rn tbldetail ) update q set rank = rn

sql sql-server stored-procedures