Solved

# Subquery on the same table

Posted on 2013-06-20
268 Views
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
Question by:countrymeister
• 2

LVL 11

Expert Comment

ID: 39263371
``````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

LVL 11

Accepted Solution

Louis01 earned 500 total points
ID: 39263375
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

### Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.