1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

replace null values with the previous not null value

Discussion in 'SQL PL/SQL' started by mkilq, May 29, 2012.

  1. mkilq

    mkilq Guest

    Hi all! I have a db oracle with a table as this:

    DATE PRICE
    1/3/2011 1,234
    4/5/2011 1,344
    11/5/2011 -
    13/7/2011 2,569
    23/9/2011 3,865
    24/9/2011 -
    3/10/2011 -
    16/11/2011 4,568

    I want to do a query to replace the null values with the previous not null value obtaining this result:

    DATE PRICE
    1/3/2011 1,234
    4/5/2011 1,344
    11/5/2011 1,344
    13/7/2011 2,569
    23/9/2011 3,865
    24/9/2011 3,865
    3/10/2011 3,865
    16/11/2011 4,568

    Someone can help me? Thanks!
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    I have a table named emp which contains information as below:

    Code (Text):


    [B]Query: -[/B]

    select empno,comm from emp;

    [B]output: -[/B]

    [B]empno  comm[/B]
    7839   
    7698   
    7782    250
    7566   
    7788   
    7902   
    7369    200
    7499    300
    7521    500
    7654    400
    7844    0
    7876   
    7900   
    7934   

    14 rows selected

     
    Now as of your requirement I have replaced all the null values in comm to their previous values. If we are having null values at first itself then they are going to replace with 0. I have done this through PL/SQL Programming.


    Code (Text):
    declare
    a number;
    cursor cur_qry is
    select rownum,e.* from emp e;
    begin
    for i in cur_qry loop
    if i.rownum=1 then
    if i.comm is null then
    dbms_output.put_line(i.empno||' '||'0');
    a:=0;
    else
    dbms_output.put_line(i.empno||' '||i.comm);
    a:=i.comm;
    end if;
    else
    if i.comm is null then
    dbms_output.put_line(i.empno||' '||a);
    else
    dbms_output.put_line(i.empno||' '||i.comm);
    a:=i.comm;
    end if;
    end if;
    end loop;
    end;

    output: -
    empno comm
    7839   0
    7698   0
    7782   250
    7566   250
    7788   250
    7902   250
    7369   200
    7499   300
    7521   500
    7654   1400
    7844   0
    7876   0
    7900   0
    7934   0

     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    An interesting solution, however if we know which release of Oracle is being used and if it's 10gR2 or later then the lead() or lag() functions can be used in a standard SQL statement to do the same thing:

    Code (SQL):
    SQL> CREATE TABLE nulltest(
    2 recid NUMBER,
    3 dte DATE,
    4 price NUMBER
    5 );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
    2 INTO nulltest
    3 VALUES(1,to_date('01/03/2011','dd/mm/rrrr'), 1234)
    4 INTO nulltest
    5 VALUES(2,to_date('04/05/2011','dd/mm/rrrr'), 1344 )
    6 INTO nulltest
    7 VALUES(3,to_date('11/05/2011','dd/mm/rrrr'), NULL )
    8 INTO nulltest
    9 VALUES(4,to_date('13/07/2011','dd/mm/rrrr'), 2569)
    10 INTO nulltest
    11 VALUES(5,to_date('23/09/2011','dd/mm/rrrr'), 3865)
    12 INTO nulltest
    13 VALUES(6,to_date('24/09/2011','dd/mm/rrrr'), NULL)
    14 INTO nulltest
    15 VALUES(7,to_date('03/10/2011','dd/mm/rrrr'), NULL)
    16 INTO nulltest
    17 VALUES(8,to_date('16/11/2011','dd/mm/rrrr'), 4568)
    18 SELECT * FROM dual;
    8 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT x.dte, nvl(x.price, lag(x.price) OVER (ORDER BY x.recid)) price
    2 FROM
    3 (SELECT recid, dte, nvl(price, lag(price) OVER (ORDER BY recid)) price
    4 FROM nulltest) x;
     

    DTE            PRICE
    --------- ----------
    01-MAR-11       1234
    04-MAY-11       1344
    11-MAY-11       1344
    13-JUL-11       2569
    23-SEP-11       3865
    24-SEP-11       3865
    03-OCT-11       3865
    16-NOV-11       4568
     
    8 ROWS selected.
     
    SQL>
     
    As you can see you will need a record locater value other than price as ordering by price won't get the results desired. Notice also there are no extraneous 0 values/duplicated dates with this solution.
     
    kiran.marla likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Again yours is an interesting offering but it doesn't solve the problem the original poster provided as it does not replace NULL values with the last non-null value from the same column. The following code will do what the original question asked:

    Code (SQL):
    SQL> DECLARE
      2  v_prev_price NUMBER:=NULL;
      3  cursor cur_qry IS
      4  SELECT * FROM nulltest ORDER BY recid;
      5  BEGIN
      6          FOR i IN cur_qry loop
      7
      8                  IF i.price IS NOT NULL THEN
      9                          dbms_output.put_line(i.dte||'   '||i.price);
     10                          v_prev_price:=i.price;
     11                  ELSE
     12                          dbms_output.put_line(i.dte||'   '||v_prev_price);
     13                  END IF;
     14          END loop;
     15  END;
     16  /

    01-MAR-11       1234
    04-MAY-11       1344
    11-MAY-11       1344
    13-JUL-11       2569
    23-SEP-11       3865
    24-SEP-11       3865
    03-OCT-11       3865
    16-NOV-11       4568
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Thats great David, Thanks for the solution. I have tried but didn't got that point. So provided solution in PL/SQL.
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi david,

    I have given solution whenever there is null at first record also it replaces with 0. It works fine also David. I have tested that one as well. In your solution if we are having first record as null or first few records as null then it doesn't works fine. So I have written in that manner.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I think that it would have helped you tremendously to create a test table and use the sample data provided. From that example the first value in the table cannot be NULL thus lag() works without issue; only unchanging price values are set to NULL for a given date thus there must always be a PRICE value for the earliest recorded date/time. Notice my solution in PL/SQL; it's much shorter than yours (because I used the sample data) and outputs the same results as the plain SQL query since I was able to store the prior PRICE value to use when PRICE is NULL (which is due to how the sample data is generated, which is not the same as the data in the EMP table).

    We learn by doing and I'm certain you learned much from this exercise. Sometimes it helps to have someone point out aspects of a problem that were missed in the proposed solution.

    Keep up the good work and keep learning and posting.
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Is it possible to work on with your query when we are having null values for first 2 to 3 records in table or we need to write different query.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot use lag() but you could use lead() to get the values however that would replace all null values with the next non-null value rather than the previous non-null value:

    Code (SQL):
    SQL> INSERT INTO nulltest
      2  VALUES(0, sysdate, NULL);
     
    1 ROW created.
     
    SQL>
    SQL> SELECT x.dte, nvl(x.price, lead(x.price) OVER (ORDER BY x.recid)) price
      2  FROM
      3  (SELECT recid, dte, nvl(price, lead(price) OVER (ORDER BY recid)) price
      4  FROM nulltest) x;
     
    DTE            PRICE
    --------- ----------
    29-MAY-12       1234
    01-MAR-11       1234
    04-MAY-11       1344
    11-MAY-11       2569
    13-JUL-11       2569
    23-SEP-11       3865
    24-SEP-11       4568
    03-OCT-11       4568
    16-NOV-11       4568
     
    9 ROWS selected.
     
    SQL>
     
    Bharat likes this.
  10. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Thanks alot David. Iam bit unfamiliar with this concept these days and thanks for refreshing me with them. Can I know how to write both of them in single query just like lead and lag.

    And one more is I have inserted few more lines just as 2nd coding part below: -

    Your code is below:

    Code (Text):


    SQL> CREATE TABLE nulltest(
    2 recid NUMBER,
    3 dte DATE,
    4 price NUMBER
    5 );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
    2 INTO nulltest
    3 VALUES(1,to_date('01/03/2011','dd/mm/rrrr'), 1234)
    4 INTO nulltest
    5 VALUES(2,to_date('04/05/2011','dd/mm/rrrr'), 1344 )
    6 INTO nulltest
    7 VALUES(3,to_date('11/05/2011','dd/mm/rrrr'), NULL )
    8 INTO nulltest
    9 VALUES(4,to_date('13/07/2011','dd/mm/rrrr'), 2569)
    10 INTO nulltest
    11 VALUES(5,to_date('23/09/2011','dd/mm/rrrr'), 3865)
    12 INTO nulltest
    13 VALUES(6,to_date('24/09/2011','dd/mm/rrrr'), NULL)
    14 INTO nulltest
    15 VALUES(7,to_date('03/10/2011','dd/mm/rrrr'), NULL)
    16 INTO nulltest
    17 VALUES(8,to_date('16/11/2011','dd/mm/rrrr'), 4568)
    18 SELECT * FROM dual;
    8 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT x.dte, nvl(x.price, lag(x.price) OVER (ORDER BY x.recid)) price
    2 FROM
    3 (SELECT recid, dte, nvl(price, lag(price) OVER (ORDER BY recid)) price
    4 FROM nulltest) x;
     
     
    DTE            PRICE
    --------- ----------
    01-MAR-11 1234
    04-MAY-11 1344
    11-MAY-11 1344
    13-JUL-11 2569
    23-SEP-11 3865
    24-SEP-11 3865
    03-OCT-11 3865
    16-NOV-11 4568
     
    8 ROWS selected.

     
    Then I have inserted few more records and tried the query as below: -
    Code (Text):
     
    SQL>INSERT ALL
    2 INTO nulltest
    3 VALUES(9,to_date('01/03/2011','dd/mm/rrrr'), null)
    4 INTO nulltest
    5 VALUES(10,to_date('04/05/2011','dd/mm/rrrr'), 1344 )
    6 INTO nulltest
    7 VALUES(11,to_date('11/05/2011','dd/mm/rrrr'), NULL )
    8 INTO nulltest
    9 VALUES(12,to_date('13/07/2011','dd/mm/rrrr'), 2569)
    10 INTO nulltest
    11 VALUES(13,to_date('23/09/2011','dd/mm/rrrr'), null)
    12 INTO nulltest
    13 VALUES(14,to_date('24/09/2011','dd/mm/rrrr'), NULL)
    14 INTO nulltest
    15 VALUES(15,to_date('03/10/2011','dd/mm/rrrr'), NULL)
    16 INTO nulltest
    17 VALUES(16,to_date('16/11/2011','dd/mm/rrrr'), null)
    18 SELECT * FROM dual;

    SQL>commit;

    SQL>
    SQL> SELECT x.dte, nvl(x.price, lag(x.price) OVER (ORDER BY x.recid)) price
    2 FROM
    3 (SELECT recid, dte, nvl(price, lag(price) OVER (ORDER BY recid)) price
    4 FROM nulltest) x;
     
    Output: -
    DTE                     PRICE
    03/01/2011 00:00:00 1234
    05/04/2011 00:00:00 1344
    05/11/2011 00:00:00 1344
    07/13/2011 00:00:00 2569
    09/23/2011 00:00:00 3865
    09/24/2011 00:00:00 3865
    10/03/2011 00:00:00 3865
    11/16/2011 00:00:00 4568
    03/01/2011 00:00:00 4568
    05/04/2011 00:00:00 1344
    05/11/2011 00:00:00 1344
    07/13/2011 00:00:00 2569
    09/23/2011 00:00:00 2569
    09/24/2011 00:00:00 2569
    10/03/2011 00:00:00
    11/16/2011 00:00:00




     
    By this the query doesn't pulls records for last two records as shown above. For this we need to write so many sub queries here just like below

    Code (Text):

    SELECT dte, nvl(price, lag(price) OVER (ORDER BY recid)) price from
    (
    SELECT recid,dte, nvl(price, lag(price) OVER (ORDER BY recid)) price from
    (
    SELECT x.recid,x.dte, nvl(x.price, lag(x.price) OVER (ORDER BY x.recid)) price
     FROM
     (SELECT recid, dte, nvl(price, lag(price) OVER (ORDER BY recid)) price
     FROM nulltest) x));

    Output: -

    DTE                      PRICE
    03/01/2011 00:00:00 1234
    05/04/2011 00:00:00 1344
    05/11/2011 00:00:00 1344
    07/13/2011 00:00:00 2569
    09/23/2011 00:00:00 3865
    09/24/2011 00:00:00 3865
    10/03/2011 00:00:00 3865
    11/16/2011 00:00:00 4568
    03/01/2011 00:00:00 4568
    05/04/2011 00:00:00 1344
    05/11/2011 00:00:00 1344
    07/13/2011 00:00:00 2569
    09/23/2011 00:00:00 2569
    09/24/2011 00:00:00 2569
    10/03/2011 00:00:00 2569
    11/16/2011 00:00:00 2569

     
    But we don't know how many records are there like this. So is it possible to write in any other way.
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The lag() and lead() functions can only do so much before limitations arise; with your last example the only reliable recourse is the PL/SQL I posted earlier:

    Code (SQL):
    SQL> DECLARE
      2  v_prev_price NUMBER:=NULL;
      3  cursor cur_qry IS
      4  SELECT * FROM nulltest ORDER BY recid;
      5  BEGIN
      6          FOR i IN cur_qry loop
      7
      8                  IF i.price IS NOT NULL THEN
      9                          dbms_output.put_line(i.dte||'   '||i.price);
     10                          v_prev_price:=i.price;
     11                  ELSE
     12                          dbms_output.put_line(i.dte||'   '||v_prev_price);
     13                  END IF;
     14          END loop;
     15  END;
     16  /
    01-MAR-11       1234
    04-MAY-11       1344
    11-MAY-11       1344
    13-JUL-11       2569
    23-SEP-11       3865
    24-SEP-11       3865
    03-OCT-11       3865
    16-NOV-11       4568
    01-MAR-11       4568
    04-MAY-11       1344
    11-MAY-11       1344
    13-JUL-11       2569
    23-SEP-11       2569
    24-SEP-11       2569
    03-OCT-11       2569
    16-NOV-11       2569
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
     
    Bharat likes this.
  12. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    LAST_VALUE analytic Function with IGNORE NULLS Cluause should work for you.

    Code (SQL):
    SQL> SELECT * FROM nulltest x ORDER BY 1;

         RECID DTE            PRICE
    ---------- --------- ----------
             1 01-MAR-11       1234
             2 04-MAY-11       1344
             3 11-MAY-11
             4 13-JUL-11       2569
             5 23-SEP-11       3865
             6 24-SEP-11
             7 03-OCT-11
             8 16-NOV-11       4568
             9 01-MAR-11
            10 04-MAY-11       1344
            11 11-MAY-11
            12 13-JUL-11       2569
            13 23-SEP-11
            14 24-SEP-11
            15 03-OCT-11
            16 16-NOV-11

    16 ROWS selected.

    SQL> SELECT recid, dte,
      2  LAST_VALUE(x.price IGNORE NULLS) OVER (ORDER BY x.recid ) newprice
      3  FROM nulltest x;

         RECID DTE         NEWPRICE
    ---------- --------- ----------
             1 01-MAR-11       1234
             2 04-MAY-11       1344
             3 11-MAY-11       1344
             4 13-JUL-11       2569
             5 23-SEP-11       3865
             6 24-SEP-11       3865
             7 03-OCT-11       3865
             8 16-NOV-11       4568
             9 01-MAR-11       4568
            10 04-MAY-11       1344
            11 11-MAY-11       1344
            12 13-JUL-11       2569
            13 23-SEP-11       2569
            14 24-SEP-11       2569
            15 03-OCT-11       2569
            16 16-NOV-11       2569

    16 ROWS selected.

    SQL>
     
    zargon likes this.
  13. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Great work raj, If we are having null at first record then we can use nvl for our last_value function as shown below:

    Code (Text):

    SELECT empno, deptno,
        nvl(LAST_VALUE(x.comm IGNORE NULLS) OVER (ORDER BY x.empno ),0) newprice
       FROM emp x;
     
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That depends on what you want the first value to be:

    Code (SQL):
    SQL> SELECT recid, dte,
      2  LAST_VALUE(x.price IGNORE NULLS) OVER (ORDER BY x.recid ) newprice
      3  FROM nulltest x;
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
             0 01-FEB-11
             1 01-MAR-11       1234
             2 04-MAY-11       1344
             3 11-MAY-11       1344
             4 13-JUL-11       2569
             5 23-SEP-11       3865
             6 24-SEP-11       3865
             7 03-OCT-11       3865
             8 16-NOV-11       4568
             9 01-MAR-11       4568
            10 04-MAY-11       1344
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
            11 11-MAY-11       1344
            12 13-JUL-11       2569
            13 23-SEP-11       2569
            14 24-SEP-11       2569
            15 03-OCT-11       2569
            16 16-NOV-11       2569
     
    17 ROWS selected.
     
    SQL>
    SQL> SELECT recid, dte,
      2  nvl(LAST_VALUE(x.price IGNORE NULLS) OVER (ORDER BY x.recid ), 0) newprice
      3  FROM nulltest x;
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
             0 01-FEB-11          0
             1 01-MAR-11       1234
             2 04-MAY-11       1344
             3 11-MAY-11       1344
             4 13-JUL-11       2569
             5 23-SEP-11       3865
             6 24-SEP-11       3865
             7 03-OCT-11       3865
             8 16-NOV-11       4568
             9 01-MAR-11       4568
            10 04-MAY-11       1344
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
            11 11-MAY-11       1344
            12 13-JUL-11       2569
            13 23-SEP-11       2569
            14 24-SEP-11       2569
            15 03-OCT-11       2569
            16 16-NOV-11       2569
     
    17 ROWS selected.
     
    SQL>
    SQL> SELECT recid, dte,
      2  nvl(LAST_VALUE(x.price IGNORE NULLS) OVER (ORDER BY x.recid ), lead(x.price) OVER (ORDER BY x.recid)) newprice
      3  FROM nulltest x;
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
             0 01-FEB-11       1234
             1 01-MAR-11       1234
             2 04-MAY-11       1344
             3 11-MAY-11       1344
             4 13-JUL-11       2569
             5 23-SEP-11       3865
             6 24-SEP-11       3865
             7 03-OCT-11       3865
             8 16-NOV-11       4568
             9 01-MAR-11       4568
            10 04-MAY-11       1344
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
            11 11-MAY-11       1344
            12 13-JUL-11       2569
            13 23-SEP-11       2569
            14 24-SEP-11       2569
            15 03-OCT-11       2569
            16 16-NOV-11       2569
     
    17 ROWS selected.
     
    SQL>
     
     
    Bharat likes this.
  15. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi all,

    According to the David's post I posted his solution again here with problem statement.

    Here is the problem statement: -

    Code (Text):

    I have a db oracle with a table as this:

    DATE PRICE
    1/3/2011 1,234
    4/5/2011 1,344
    11/5/2011 -
    13/7/2011 2,569
    23/9/2011 3,865
    24/9/2011 -
    3/10/2011 -
    16/11/2011 4,568

    I want to do a query to replace the null values with the previous not null value obtaining this result:

    DATE PRICE
    1/3/2011 1,234
    4/5/2011 1,344
    11/5/2011 1,344
    13/7/2011 2,569
    23/9/2011 3,865
    24/9/2011 3,865
    3/10/2011 3,865
    16/11/2011 4,568

     
    Solution for this: -

    Code (Text):


    SQL> SELECT recid, dte,
      2  LAST_VALUE(x.price IGNORE NULLS) OVER (ORDER BY x.recid ) newprice
      3 FROM nulltest x;
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
             0 01-FEB-11
             1 01-MAR-11 1234
             2 04-MAY-11 1344
             3 11-MAY-11 1344
             4 13-JUL-11 2569
             5 23-SEP-11 3865
             6 24-SEP-11 3865
             7 03-OCT-11 3865
             8 16-NOV-11 4568
             9 01-MAR-11 4568
            10 04-MAY-11 1344
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
            11 11-MAY-11 1344
            12 13-JUL-11 2569
            13 23-SEP-11 2569
            14 24-SEP-11 2569
            15 03-OCT-11 2569
            16 16-NOV-11 2569
     
    17 ROWS selected.
     
    SQL>
    SQL> SELECT recid, dte,
      2  nvl(LAST_VALUE(x.price IGNORE NULLS) OVER (ORDER BY x.recid ), 0) newprice
      3 FROM nulltest x;
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
             0 01-FEB-11 0
             1 01-MAR-11 1234
             2 04-MAY-11 1344
             3 11-MAY-11 1344
             4 13-JUL-11 2569
             5 23-SEP-11 3865
             6 24-SEP-11 3865
             7 03-OCT-11 3865
             8 16-NOV-11 4568
             9 01-MAR-11 4568
            10 04-MAY-11 1344
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
            11 11-MAY-11 1344
            12 13-JUL-11 2569
            13 23-SEP-11 2569
            14 24-SEP-11 2569
            15 03-OCT-11 2569
            16 16-NOV-11 2569
     
    17 ROWS selected.
     
    SQL>
    SQL> SELECT recid, dte,
      2  nvl(LAST_VALUE(x.price IGNORE NULLS) OVER (ORDER BY x.recid ), lead(x.price) OVER (ORDER BY x.recid)) newprice
      3 FROM nulltest x;
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
             0 01-FEB-11 1234
             1 01-MAR-11 1234
             2 04-MAY-11 1344
             3 11-MAY-11 1344
             4 13-JUL-11 2569
             5 23-SEP-11 3865
             6 24-SEP-11 3865
             7 03-OCT-11 3865
             8 16-NOV-11 4568
             9 01-MAR-11 4568
            10 04-MAY-11 1344
     
         RECID DTE         NEWPRICE
    ---------- --------- ----------
            11 11-MAY-11 1344
            12 13-JUL-11 2569
            13 23-SEP-11 2569
            14 24-SEP-11 2569
            15 03-OCT-11 2569
            16 16-NOV-11 2569
     
    17 ROWS selected.
     
    SQL>