By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,481 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,481 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Jan 18 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jan 18 '07 #2

P: n/a


On Jan 18, 7:15 pm, "jmoore1...@msn.com" <JMoore2...@gmail.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),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

Jan 19 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.