Forgive what may be a silly question, but I'm not much of a database guru.
Here is my table :
id_data | val_no3 | id_prev | id_next
--------+---------+---------+----------
1 | | | 2
2 | 7 | |
3 | | 2 | 4
4 | 5 | |
5 | | 4 | 10
6 | | 4 | 10
7 | | 4 | 10
8 | | 4 | 10
9 | | 4 | 10
10 | 8 | 4 |
In the table below :
id_prev is the value of the id_data which precedes when val_no3 is null
id_next is the value of the id_data which folow when val_no3 is null
And now i would like to have this one :
id_data | val_no3 | id_prev | id_next | val_prev | val_next
--------+---------+---------+----------+----------+----------
1 | | | 2 | | 7
2 | 7 | | | |
3 | | 2 | 4 | 7 | 5
4 | 5 | | | |
5 | | 4 | 10 | 5 | 8
6 | | 4 | 10 | 5 | 8
7 | | 4 | 10 | 5 | 8
8 | | 4 | 10 | 5 | 8
9 | | 4 | 10 | 5 | 8
10 | 8 | | | |
The conditions are as follows:
If val_no3 is null then : val_prev and val_next must be null
If val_no3 is not null then :
val_prev must be equal to the previous value of val_no3 (it should be null if val_no3 which precedes is null too)
val_next must be equal to the following value of val_no3 (it should be null if val_no3 which folows is null too)
I think i might have to use something with lag and lead but i don't know how to do.
I would be very grateful if you could give me your help to resolve this issue, thank you.
No need for analytic functions, just sub-selects. Something like the following (untested) should work: