| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Analytical Function: select last value that is not null
Marcel,
How about something like this...
ops$clbeck_at_ORA8I.WORLD> l
1 select d,
2 nvl( n, ( select distinct last_value( x1.n ) over () 3 from x x1 4 where x1.n is not null 5 and x1.d < x2.d )) n6 from x x2
D N
05.04.2000 06.04.2000 07.04.2000 7 08.04.2000 7 09.04.2000 7 10.04.2000 7 11.04.2000 7 12.04.2000 7 13.04.2000 7 14.04.2000 8 15.04.2000 8 16.04.2000 8 17.04.2000 9 18.04.2000 9
14 rows selected.
hope this helps.
chris.
-- Christopher Beck, Principal Technologist, Oracle Corporation christopher.beck_at_oracle.com Beginning Oracle Programming, http://www.amazon.com/exec/obidos/ASIN/186100690X Opinions are mine and do not necessarily reflect those of Oracle Corp "Marcel Kraupp" <marcel.kraupp_at_gmx.ch> wrote in message news:332bb004.0205100805.7eb9bc5a_at_posting.google.com...Received on Fri May 10 2002 - 12:42:09 CDT
> 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
 |
 |