JustSomeNewbie JustSomeNewbie - 1 year ago 62
SQL Question

Using value from previous row

I'm having some problem with using value from previous row (i kinda don't know how to do it).

I want to do something like :

update test SET amount = (select amountAfter
from rozl (Here i want to take it from previous row = this ID - 1)

I got primary key id, whole table is sorted by id.
What I want to do is :

Take value from previous row from Column name amountAfter and insert it into amount in actual id.

Here is example

id amount used destroyed amountAfter
1 100 50 30 20
2 20 5 1 14

Answer Source

Here's one option using a join assuming your id fields are sequential:

update t1
set t1.amount = t2.amountafter
from test t1 join
     test t2 on t2.id = t1.id - 1

If you are using 2012 or higher, look at using lag:

with cte as (
    select id, amount, lag(amount) over (order by id) prevamount
    from test
update cte 
set amount = prevamount 
where prevamount is not null

And I guess to be complete, 2008 would work with row_number if the numbers aren't sequential (using a combination of both approaches):

with cte as (
    select id, amount, row_number() over (order by id) rn
    from test
update t1
set t1.amount = t2.amount
from cte t1 join
     cte t2 on t2.rn = t1.rn - 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download