PostgreSQL – Get Values of Two Rows Before Selected Row

postgresqlselectupdate

I want to update an row according to the values of two rows before the row how can i do that?

Let's assume my table looks like this

ID         amount

1           1
2           3
7           5

And i want to do this

UPDATE amount 
FROM table 
SET amount = (AmounToFRowOneBefore + AmounToFRowTwoBefore)/2  
WHERE ID=7;

Expected output would be

 ID         amount

    1           1
    2           3
    7           2

And if I do this I don't know anything about the two rows before so no ID or anything is there a query to select the values before the selected row?

Best Answer

You can use either a window function:

with ct as
(
  select
      id,
      amount,
      (sum(amount) over (order by id rows between 2 preceding and 1 preceding)) / 2 as new_amount
  from
      t
)
update t
set    amount = ct.new_amount
from
    ct
where
    t.id = ct.id
    and ct.id = 3;

Or use a subquery that returns the 2 previous rows:

update t
set amount = (select sum(t3.amount) / 2
             from (select amount 
                   from t t2
                   where t2.id < t.id 
                   order by id desc 
                   limit 2) t3)
where
    t.id = 3;

Check them for performance.

select * from t;

id |             amount
-: | -----------------:
 1 |                  1
 2 |                  3
 3 | 2.0000000000000000

db<>fiddle here

Related Question