[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 279

# Subquery on the same table

I have a table with four columns

Code
Value
Rank
RollingValue

I want to update the RollingValue to the Value plus the Value in the previous Rank
Initially all the values in the RollingVale are zero

Code   Value      Rank       RollingValue
ABCD   7            1            0
ABCD   21       2            0
ABCD   10            3            0

Code   Value      Rank       RollingValue
ABCD   7            1            7
ABCD   21       2            28
ABCD   10            3            38

I tried this

UPDATE TableT
SET RollingValue = t.Value + p.Value
FROM  TableT  t
INNER JOIN
( Select Code, Value, Rank from TableT) AS  p
ON t.Code = p.Code
WHERE (t.Rank = (p.Rank + 1))
0
countrymeister
• 2
1 Solution

Commented:
``````declare @TableT TABLE (Code char(4) not null
,Value int not null
,[Rank] int not null
,RollingValue int not null)

insert into @TableT (Code,Value,[Rank],RollingValue) values ('ABCD', 7, 1, 0)
insert into @TableT (Code,Value,[Rank],RollingValue) values ('ABCD', 21, 2, 0)
insert into @TableT (Code,Value,[Rank],RollingValue) values ('ABCD', 10, 3, 0)

update t1
set t1.RollingValue = (select SUM(t2.Value)
from @TableT t2
where t2.[Rank] <= t1.[Rank])
from @TableT t1

select * from @TableT
``````
0

Commented:
Example of how you'll select it:
``````select t2.Code
, t2.Value
, t2.[Rank]
, SUM(t1.Value) as RollingValue
from @TableT t1 right outer join @TableT t2
on t1.[Rank] <= t2.[Rank]
group by t2.Code
, t2.Value
, t2.[Rank]
order by t2.[Rank]
``````
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.