Home » SQL & PL/SQL » SQL & PL/SQL » Continous LAG when null (9i, 10g)
Continous LAG when null [message #329039] Mon, 23 June 2008 20:45 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Just want to inquire if it's possible in sql to continously get the previously NOT NULL value then copy it to the current null value. I tried doing this using LAG, but I can only do it with the first row after the not null row. (I'll post later some sample).

For this I have a table for example with a column price. ex:

(not yet run)


create table test(price number);

insert into test values(1);
insert into test values(null);
insert into test values(null);
insert into test values(2);
insert into test values(null);
insert into test values(null);

--draft only
select price, lag(price, 1, null)over() lag



Then the desired output should be


PRICE
- - - 
1
1
1
2
2
2



Wherein the null rows will get the previous not null value. Hope I've explained it clearly. Thanks.

Regards,
Rhani
Re: Continous LAG when null [message #329040 is a reply to message #329039] Mon, 23 June 2008 20:57 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
In any RDBMS, the rows in a table have NO inherent order!
The order in which rows are inserted do NOT dictate the order in which the rows are returned (without the use of ORDER BY clause).
Re: Continous LAG when null [message #329059 is a reply to message #329039] Mon, 23 June 2008 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to Ana's answer:
Quote:
I tried doing this using LAG

This is a good way but if you read the documentation on the function you'll see the ORDER BY clause is mandatory.

Regards
Michel

[Updated on: Mon, 23 June 2008 23:31]

Report message to a moderator

Re: Continous LAG when null [message #329161 is a reply to message #329039] Tue, 24 June 2008 06:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've rewritten your test data to add in a column to order by, and you can do it like this:
drop table lag_Test;

create table lag_test(price number, ord number);

insert into lag_test values(1,1);
insert into lag_test values(null,2);
insert into lag_test values(null,3);
insert into lag_test values(3,4);
insert into lag_test values(null,5);
insert into lag_test values(1,6);
insert into lag_test values(null,7);


select max(price) over (partition by grp) nn_price
      ,price
      ,ord
      ,grp
from (select price
            ,ord
            ,count(price) over (order by ord) grp
      from lag_test)
;

  NN_PRICE      PRICE        ORD        GRP
---------- ---------- ---------- ----------
         1          1          1          1
         1 null                2          1
         1 null                3          1
         3          3          4          2
         3 null                5          2
         1          1          6          3
         1 null                7          3
Re: Continous LAG when null [message #329169 is a reply to message #329059] Tue, 24 June 2008 07:16 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Michel Cadot wrote on Tue, 24 June 2008 12:31
In addition to Ana's answer:
Quote:
I tried doing this using LAG

This is a good way but if you read the documentation on the function you'll see the ORDER BY clause is mandatory.

Regards
Michel




Yes, I know the order by, that's why I note that the code given was only a sample/draft/incomplete, I just didn't had access to a database when I posted this. I just want to give first some initial explanation. Thanks for replying.
Re: Continous LAG when null [message #329170 is a reply to message #329161] Tue, 24 June 2008 07:18 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
JRowbottom wrote on Tue, 24 June 2008 19:16
I've rewritten your test data to add in a column to order by, and you can do it like this:
drop table lag_Test;

create table lag_test(price number, ord number);

insert into lag_test values(1,1);
insert into lag_test values(null,2);
insert into lag_test values(null,3);
insert into lag_test values(3,4);
insert into lag_test values(null,5);
insert into lag_test values(1,6);
insert into lag_test values(null,7);


select max(price) over (partition by grp) nn_price
      ,price
      ,ord
      ,grp
from (select price
            ,ord
            ,count(price) over (order by ord) grp
      from lag_test)
;

  NN_PRICE      PRICE        ORD        GRP
---------- ---------- ---------- ----------
         1          1          1          1
         1 null                2          1
         1 null                3          1
         3          3          4          2
         3 null                5          2
         1          1          6          3
         1 null                7          3





Thank you so much Sir, I'll check this logic.


Update...

This solved the grouping part Sir. Thank you very much.


      select price
            ,ord
            ,count(price) over (order by ord) grp
      from lag_test

[Updated on: Tue, 24 June 2008 07:28]

Report message to a moderator

Re: Continous LAG when null [message #329180 is a reply to message #329169] Tue, 24 June 2008 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ehegagoka wrote on Tue, 24 June 2008 14:16
Yes, I know the order by, that's why I note that the code given was only a sample/draft/incomplete, I just didn't had access to a database when I posted this. I just want to give first some initial explanation. Thanks for replying.

What I wanted to empahize is that you need to define an order to be able to use "order by" clause and so lag function.

Regards
Michel

[Updated on: Tue, 24 June 2008 08:13]

Report message to a moderator

Re: Continous LAG when null [message #329186 is a reply to message #329180] Tue, 24 June 2008 08:05 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Thank you very much. I'll note that. Thanks again Mam.

Regards,
Rhani
Previous Topic: Last Full 52 Weeks
Next Topic: sort a column
Goto Forum:
  


Current Time: Sat Dec 10 18:32:33 CST 2016

Total time taken to generate the page: 0.04461 seconds