Posted by Ed Murphy on 01/18/07 20:26

jmoore1999@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

• Next in forum: Re: Bizarre slow query problem (again)
• Prev in forum: Re: Query Question
• Thread view: Re: Query which calculates a field value based on previous row's values

[Reply to this message]