col2=col1+max(col2) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

col2=col1+max(col2)

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2010-11-15 : 15:25:31

hi

i have a table like this

row col1 col2
--- ---- ----
1 10 0
2 12 0
3 15 0
4 -10 0
5 17 0
. . .
. . .
. . .

i want a result update this table with formula :
col2=col1+max(col2)
row col1 col2
--- ---- ----
1 10 10
2 12 22
3 15 37
4 -10 27
5 17 54
. . .
. . .
. . .

its very urgent please help
thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-15 : 15:39:18

Your desired output appears to be a running total. It doesn't seem to be what you asked for: col1+max(col2)
Search for "Running Total" here. There are lots of topics

Be One with the Optimizer
TG

fan2005
Yak Posting Veteran

85 Posts

Posted - 2010-11-15 : 15:46:58

col2=col1+max(col2)
row col1 col2
--- ---- ----
1 10 10
2 12 22
3 15 37
4 -10 27
5 17 54
. . .
here we dint add 17+27
i added 17+(max(col2)=37) = 54

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-15 : 15:54:57

You will still need to implement some sort of running total because the col2 values need to be re-evaluated for each row.

EDIT:
check out this article:
http://www.sqlteam.com/article/calculating-running-totals

looks like in solution2 you just need to change the SUM to MAX

Be One with the Optimizer
TG

fan2005
Yak Posting Veteran

85 Posts

Posted - 2010-11-15 : 16:16:41

it doesn't work this way
it needs to update col2 to calculate max (col2)
please help

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-11-15 : 16:29:36

quote:

row col1 col2
--- ---- ----
1 10 0
2 12 0
3 15 0
4 -10 0
5 17 0

ALL col2 equal 0 in the table?

fan2005
Yak Posting Veteran

85 Posts

Posted - 2010-11-15 : 16:56:20

may or may not

fan2005
Yak Posting Veteran

85 Posts

Posted - 2010-11-15 : 16:59:12

lets say yes
then what is the solution

fan2005
Yak Posting Veteran

85 Posts

Posted - 2010-11-15 : 17:47:31

I think it should be recursive
please help with the query
thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-15 : 17:58:52

There are a bunch of ways to do this. Here is one way using a WHILE loop:

DECLARE @T TABLE(row int, col1 int, col2 int)

INSERT @T (row, col1, col2) VALUES
(1, 10, 0), 
(2, 12, 0),
(3, 15, 0),
(4, -10, 0),
(5, 17, 0)


SELECT * FROM @T

DECLARE @Row INT

SET @Row = (SELECT MIN(row) FROM @T)

WHILE @Row IS NOT NULL
BEGIN
    UPDATE
        @T
    SET
        col2 = col1 + (SELECT MAX(col2) FROM @T) 
    WHERE
        row = @Row
        
    SET @Row = (SELECT MIN(row) FROM @T WHERE row > @Row)  
END


SELECT * FROM @T

fan2005
Yak Posting Veteran

85 Posts

Posted - 2010-11-15 : 18:13:10

that's great
thank you very much

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 03:28:53

Try this too
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-26 : 02:22:16

declare @t table
(
row int identity(1,1) ,
col1 int,
col2 int
)

insert into @t(col1,col2)
values(10,0),(12,0),(15,0),(-10,0),(17,0)

select *
from @t t1
cross apply
(
select [col3]=sum(case when col1 >0 then col1 else 0 end)
from @t t2
where t2.row <=t1.row
)t2

Iam a slow walker but i never walk back

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-26 : 09:31:02

quote:
Originally posted by dineshrajan_it

declare @t table
(
row int identity(1,1) ,
col1 int,
col2 int
)

insert into @t(col1,col2)
values(10,0),(12,0),(15,0),(-10,0),(17,0)

select *
from @t t1
cross apply
(
select [col3]=sum(case when col1 >0 then col1 else 0 end)
from @t t2
where t2.row <=t1.row
)t2

Iam a slow walker but i never walk back

This type of VALUES caluse wont work in versions prior to 2008

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources