Query which calculates a field value based on previous row's values

[jmoore1999]

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

[Ed Murphy]

jm********@msn. 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.

If you want Name6 to be ranked 6th, then I think this will work:

select t.Full_Name
(select count(*) + 1
from The_Table t2
where t2.Points t.Points) as Rank
from The_Table t

[PDreyer]

On Jan 18, 7:15 pm, "jmoore1...@msn .com" <JMoore2...@gma il.comwrote:

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),poi nts 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=ide ntity(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.poin ts

drop table #t1
drop table #t2