Postgresql - update a row based upon last highest value from the same table

Question

I have a table with columns:

id (PK),
low_value,
high_value

Id and high_value is populated in this table and low_value needs to be populated using populated data. Low_value should be the previous ordered high_value in the table.

Example - table data before updating low_value:

id     | low_value      | high_value
---------------------------------------
1      |                | 1000
2      |                | 1000  
3      |                | 2000  
4      |                | 5000  
5      |                | 7000  
6      |                | 10000 
7      |                | 10000 
8      |                | 20000

table data after updating low_value should be:

id     | low_value      | high_value
---------------------------------------
1      |    0           | 1000
2      |    0           | 1000  
3      |    1000        | 2000  
4      |    2000        | 5000  
5      |    5000        | 7000  
6      |    7000        | 10000 
7      |    7000        | 10000 
8      |    10000       | 20000

I am thinking of assigning a rank (using window function rank()) to each row based upon high_value, so rows with same high_value will have same rank and then pick the value with rank less than current rank for the row getting updated. But i am not able to construct the sql. Can anyone help with the sql with or without ranking rows ?

Gordon Linoff · Accepted Answer · 2021-05-27 12:33:55Z

2

Hmmm . . . it is a little strange that there are duplicates. Assuming the values are ordered, you can use:

update t
    set low_value = coalesce(prev_high_value, 0)
    from (select high_value,
                 lag(high_value) over (order by high_value) as prev_high_value
          from t
          group by high_value
         ) tt
    where tt.high_value = t.high_value

edited May 27, 2021 at 12:33

answered May 26, 2021 at 14:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user10916892 Over a year ago

Thanks for posting, but this is not working, for first 10000 it has prev_high_value = 7000 but for next 7000 it is 7000 itself.

Gordon Linoff Over a year ago

@user10916892 . . . As written, there was a typo and it should not have run. However, is it possible that you are storing the value as a string and not as a number? If so, you should fix the data. Do not store numbers as strings!

forpas · Accepted Answer · 2021-05-26 15:25:07Z

1

Since you have duplicate high_values in consecutive ids LAG() will not always return the correct value.
For each row you must get the high_value of the row with the max id with a different high_value than the current row:

WITH cte AS (
  SELECT *, SUM(flag) OVER (ORDER BY id) grp
  FROM (
    SELECT *, 
           LAG(high_value, 1, 0) OVER (ORDER BY id) prev_high_value,
           (high_value <> LAG(high_value, 1, 0) OVER (ORDER BY id))::int flag
    FROM tablename
  ) t  
)
UPDATE tablename t
SET low_value = new_value
FROM (
  SELECT *, FIRST_VALUE(prev_high_value) OVER (PARTITION BY grp ORDER BY flag DESC) new_value
  FROM cte
) c
WHERE t.id = c.id

See the demo.

answered May 26, 2021 at 15:25

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

Postgresql - update a row based upon last highest value from the same table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related