Re: Analytical Function: select last value that is not null

How about:

SELECT d,

       SUM(n) over (PARTITION BY f) status   FROM (
SELECT x.*,

       SUM(n) over (ORDER BY d) f
  FROM x ) v
 ORDER BY d

I LOVE analytical functions!!!

On 10 May 2002 09:05:08 -0700, marcel.kraupp_at_gmx.ch (Marcel Kraupp) wrote:

>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

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat May 11 2002 - 11:50:33 CDT