Re: Analytics/update question

Barbara Boehmer schrieb:
> Or, using the lag analytic function:
>
> scott_at_ORA92> SELECT * FROM your_table
> 2 /
>
> ID VALUE DATE_COL
> ---------- ---------- ---------
> 1 -999.99 26-sep-05
> 1 16.7 23-sep-05
> 1 8.9 22-sep-05
> 2 12.7 23-sep-05
> 2 12.8 20-sep-05
> 3 -999.99 19-sep-05
> 3 44 15-sep-05
> 3 76 14-sep-05
>
> 8 rows selected.
>
> scott_at_ORA92> UPDATE your_table o
> 2 SET o.value =
> 3 (SELECT i.lag_val
> 4 FROM (SELECT id, value, date_col,
> 5 LAG (value) OVER
> 6 (PARTITION BY id
> 7 ORDER BY date_col) AS lag_val
> 8 FROM your_table) i
> 9 WHERE i.id = o.id
> 10 AND i.value = o.value
> 11 AND i.date_col = o.date_col)
> 12 WHERE o.value = -999.99
> 13 /
>
> 2 rows updated.
>
> scott_at_ORA92> SELECT * FROM your_table
> 2 /
>
> ID VALUE DATE_COL
> ---------- ---------- ---------
> 1 16.7 26-sep-05
> 1 16.7 23-sep-05
> 1 8.9 22-sep-05
> 2 12.7 23-sep-05
> 2 12.8 20-sep-05
> 3 44 19-sep-05
> 3 44 15-sep-05
> 3 76 14-sep-05
>
> 8 rows selected.
>

<quote>
For any ID, if it's latest value is -999.99 I want to update its value to the value from its previous date
</quote>

I think this statement will update every -999.99 to his predecessor To update only if the -999.99 is the *latest within the group of id's* , i would slightly modify it:

UPDATE (
SELECT id, value, date_col,
LAG (value) OVER (PARTITION BY ID ORDER BY date_col, id DESC) AS lag_val, row_number() OVER (PARTITION BY ID ORDER BY date_col, id DESC) AS row_num, COUNT(*) OVER (PARTITION BY ID ) AS count_in_group FROM your_table
)
SET value = lag_val
WHERE value = -999.99
AND row_num = count_in_group;

Best regards

Maxim Received on Tue Sep 27 2005 - 15:08:27 CDT