Analytical Function: select last value that is not null

Hello

I have this problem (on 8i).
There is a table x having an attribute d (for date) and n (for number):

create table x (d date, n number);

I Insert some consequtive dates into that table:

alter session set nls_date_format='DD.MM.YYYY';

insert into x values ('05.04.2000', null);
insert into x values ('06.04.2000', null);
insert into x values ('07.04.2000', 7);
insert into x values ('08.04.2000', null);
insert into x values ('09.04.2000', null);
insert into x values ('10.04.2000', null);
insert into x values ('11.04.2000', null);
insert into x values ('12.04.2000', null);
insert into x values ('13.04.2000', null);
insert into x values ('14.04.2000', 8);
insert into x values ('15.04.2000', null);
insert into x values ('16.04.2000', null);
insert into x values ('17.04.2000', 9);
insert into x values ('18.04.2000', null);

What I want is to select d,n from x order by d so that it returns the last n that was not null.

05.04.2000
06.04.2000
07.04.2000          7
08.04.2000          7 <=== here
09.04.2000          7 <=== here
10.04.2000          7 <=== here
11.04.2000          7 <=== here
12.04.2000          7 <=== here
13.04.2000          7 <=== here
14.04.2000          8
15.04.2000          8 <=== here
16.04.2000          8 <=== here
17.04.2000          9
18.04.2000          9 <=== here

I figure, if this is possible, it must be achieved with analytical functions (thoug I might be wrong).
All I can come up with is

 select
   first_value(n) over
     (order by n range between 0 preceding and unbounded following) f_n,    d from x order by d)

but this doesn't help me very much as it returnes exactly the same values as select * from x;

I will appreciate it very much if someone comes up with a solution.

Thanks

Marcel Received on Fri May 10 2002 - 11:05:08 CDT