Analytics/update question

From: potter <thomas.reid_at_gmail.com>
Date: 27 Sep 2005 03:01:55 -0700
Message-ID: <1127813997.394394.236410@g44g2000cwa.googlegroups.com>

Oracle9i

Say I have the following table:

ID       Value           Date


---      ------          -----

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

For any ID, if it's latest value is -999.99 I want to update its value to the value from its previous date, so the above table would become:

ID       Value           Date


---      ------          -----

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

Is this do-able in a single SQL update statement, perhaps using analytics? Received on Tue Sep 27 2005 - 05:01:55 CDT