Geek Answers Handbook

Using the value from the previous row

I'm having a problem using the value from the previous line (I don't seem to know how to do this).

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 the primary key identifier, the whole table is sorted by id. I want to do this:

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

Here is an example

  id amount used destroyed amountAfter 1 100 50 30 20 2 20 5 1 14
+5
source share
1 answer

Here is one parameter using join , assuming your id fields are consecutive:

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

If you use 2012 or higher, look at 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 think that to complete, 2008 will work with row_number if the numbers are not 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
+3
source

More articles:


All Articles