SQL Server Performance Forum – Threads Archive
Cursor to insert sorted ranking
If anyone could help with the below query regarding TSQL Cursor it would be much appreciated. I have a table with 2 columns – Rank and PointsThe Points column is populated with different numerical values, and the Rank column is blank. I wish to set up a Cursor to that will place a "ranking number" in the appropriate row in the Rank column related to the corresponding value that is in the Points column in Descending order.
Eg. If the Points column is populated with 3 rows with the values 10,5,1 – I want the Cursor to insert in the Rank column: "1" next to 10; "2" next to 5; "3" next to 1. I have already developed the following code which (i) sorts the Points column in descending order and (ii) Cursor which populates the Rank column starting from "1" till the last row is filled. However the sorting process in (i) does not seem to be retained when the Cursor in step (ii) is run and therefore the Rank column is populated with numbers but these are not based on the descending numerical values in the Points column. And advice much appreciated
Thanks –(i)
SELECT Points
FROM Table
order by Points Desc –(ii)
DECLARE c7 CURSOR FOR
SELECT Rank
FROM Table
FOR UPDATE OF Rank declare @v1 int
set @v1=1 OPEN c7 FETCH NEXT FROM c7 WHILE @@FETCH_STATUS = 0
BEGIN UPDATE Table
SET Rank [email protected]
WHERE CURRENT OF c7 set @[email protected]+1 –increase ‘Rank’ variable by 1 FETCH NEXT FROM c7 END CLOSE c7
DEALLOCATE c7
instead of cursor try this:
declare @t1 table (points int,rank int)
insert into @t1(points) values(10)
insert into @t1(points) values(5)
insert into @t1(points) values(1)
select * from @t1 declare @t2 table(r1 int identity,p1 int)
insert into @t2
select points from @t1 order by 1 desc select * from @t2 update @t1 set rank=t2.r1
from @t1 t1
inner join @t2 t2 on
t1.points=t2.p1 select * from @t1
Where do you want to show data?
If you use front end application, do Ranking there Declare @t table(no int, Rank int)
Insert into @t(no)
Select 10 union all
Select 5 union all
Select 1
Update @t set Rank=(Select count(*) from @t where no>=T.no) from @t T
Select * from @t
Madhivanan Failing to plan is Planning to fail
If your points column has an index, a correlated subquery should work fine: SELECT Points,
((SELECT COUNT(*) FROM Table t2
WHERE t2.Points < Table.Points) + 1) Rank
FROM Table
ORDER BY Rank If you need to have a rank within a group defined by another column, like Category or something … SELECT Points, Category,
((SELECT COUNT(*) FROM Table t2
WHERE t2.Category = Table.Category AND t2.Points < Table.Points) + 1) Rank
FROM Table
ORDER BY Category, Rank
And if you have a WHERE on the main query, then add the criteria to the subquery as well.
I wrote an article about running totals which is the problem similar to yours. You can take a look there for details:http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp To answer your question: You should add "order by point desc" in your cursor definition. The cursor solution should be faster then solutions suggested in the thread.
]]>