declare @t table (RankPoints int, PersonName varchar(10))
insert @t
select 100, 'Jon' union all
select 100, 'Sam' union all
select 200, 'Andy' union all
select 201, 'July' union all
select 201, 'Tony' union all
select 10, 'Smith'

select rank() over (order by rankPoints desc)  as RankID
       ,RankPoints
       ,PersonName 
from   @t

select dense_rank() over (order by rankPoints desc) as RankID
       ,RankPoints
       ,PersonName 
from   @t

OUTPUT:
RankID               RankPoints  PersonName
-------------------- ----------- ----------
1                    201         July
1                    201         Tony
3                    200         Andy
4                    100         Jon
4                    100         Sam
6                    10          Smith


RankID               RankPoints  PersonName
-------------------- ----------- ----------
1                    201         July
1                    201         Tony
2                    200         Andy
3                    100         Jon
3                    100         Sam
4                    10          Smith

declare @t table (RankPoints int, PersonName varchar(10))
insert @t
select 100, 'Jon' union all
select 100, 'Sam' union all
select 200, 'Andy' union all
select 201, 'July' union all
select 201, 'Tony' union all
select 10, 'Smith'

select (select count(*) from @t where rankPoints > t.rankPoints) + 1 as RankID
       ,RankPoints
       ,PersonName
from   @t t
order by 1

select (select count(distinct RankPoints) from @t where rankPoints > t.rankPoints) + 1 as RankID
       ,RankPoints
       ,PersonName
from   @t t
order by 1

OUTPUT:
RankID      RankPoints  PersonName
----------- ----------- ----------
1           201         July
1           201         Tony
3           200         Andy
4           100         Jon
4           100         Sam
6           10          Smith


RankID      RankPoints  PersonName
----------- ----------- ----------
1           201         July
1           201         Tony
2           200         Andy
3           100         Jon
3           100         Sam
4           10          Smith

quote:

---

Originally posted by madhivanan

You can also play with "Quirky update" method
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by TG

quote:

---

Originally posted by madhivanan

You can also play with "Quirky update" method
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

---

Yes, this method: update <table> set <variable> = <column> = <expression>

With large amounts of data that is definately the fastest method I'm aware of. But be sure to read Jeff Moden's article(s) on this method. He's "the man" when it comes this method becuase he as done a lot of research and testing. And you will need to adjust your code depending on what sql version you're running. Guaranteeing the order of the updates is obviously critical and he has worked that out.

Be One with the Optimizer
TG

---