CODEWITHSUNDEEP
sqlpostgresql
user10916892
user10916892

Reputation: 997

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

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 ?

Upvotes: 1

Views: 222

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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

Upvotes: 2

forpas
forpas

Reputation: 164089

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.

Upvotes: 1

Related Questions