Posted by PDreyer on 01/19/07 13:41

On Jan 18, 7:15 pm, "jmoore1...@msn.com" <JMoore2...@gmail.com> wrote:
> I need to write a t-sql query that will take the value of the previous
> record into consideration before calculating the current row's new
> column value...
>
> Here's the situation...
>
> I have a query which return the following table structure...
>
> Full_Name Points
> ----------------- ------------
> Name1 855
> Name2 805
> Name3 800
> Name4 775
> Name5 775
> Name6 741
> etc.... etc...
>
> I need to create a calculated column that tells me where the person
> ranks in point position. The problem i run into is that in the
> situation where two or more people have the same point value i need the
> calculated rank column to display the same rank number (i.e. 4th or
> just "4") I'm not sure how to to take into consideration the previous
> row's point value to determine if it is the same as the current one
> being evaluated. If i new they were the same i could assign the same
> rank value (i.e. 4th or just "4").
>
> If any one has any insight that would be great.
>
> Thanks
> Jeremy

create table #t1 (name varchar(10),points int)
insert into #t1 select
'Name1',855 union all select
'Name2',805 union all select
'Name3',800 union all select
'Name4',775 union all select
'Name5',775 union all select
'Name6',741

select points,rank=identity(int,1,1)
into #t2 from #t1
group by points
order by points desc

select a.name,a.points,b.rank
from #t1 a, #t2 b
where a.points=b.points

drop table #t1
drop table #t2

[Back to original message]