sql2005 根据指定字段排序编号

sql语句如下：　　

with 表1 as
(
select 编号字段名, row_number() over(order by 排序字段名 desc) as rownum
from 表名
)
update 表1
set 编号字段名=rownum

应用场景：
通过这样的sql语句根据小组人气值对小组进行排名：

with groups as
(
select ranknum, row_number() over(order by 人气值 desc) as rownum
from club_groups
)
update groups
set ranknum=rownum