Home » SQL & PL/SQL » SQL & PL/SQL » Problem with LAG function
Problem with LAG function [message #389526] Mon, 02 March 2009 08:08 Go to next message
Buchas
Messages: 83
Registered: March 2006
Member
Hello,

Suppose I have such a table:

CREATE TABLE TEST.TEST564_T 
(COL1 NUMBER,
COL2 NUMBER
);

INSERT INTO TEST.TEST564_T (COL1, COL2) VALUES (1,2);
INSERT INTO TEST.TEST564_T (COL1, COL2) VALUES (1,3);
INSERT INTO TEST.TEST564_T (COL1, COL2) VALUES (2,5);
INSERT INTO TEST.TEST564_T (COL1, COL2) VALUES (2,6);
COMMIT;


If I run query:

Quote:

SELECT COL1, COL2, LAG(COL2)OVER(ORDER BY COL1) AS LAGGED FROM TEST.TEST564_T;



I get results:
COL1_ COL2_ LAGGED
1____ 2____ NULL
1____ 3____ 2
2____ 5____ 3
2____ 6____ 5

But I would like to get:
COL1_ COL2_ LAGGED
1____ 2____ NULL
1____ 3____ 2
1____ NULL_ 3
2____ 5____ NULL
2____ 6____ 5
2____ NULL_ 6

Is there some way? I can't think of anything... Looks like simple, but I am struck on this. Help please.
Re: Problem with LAG function [message #389527 is a reply to message #389526] Mon, 02 March 2009 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But I would like to get:

What is the rule, explain it with words.

In addition, you know to format queries, why don't you format output?

Regards
Michel
Re: Problem with LAG function [message #389528 is a reply to message #389526] Mon, 02 March 2009 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select * from t
  4      union all
  5      select distinct col1, null from t
  6    )
  7  select col1, col2, 
  8         lag (col2) over (partition by col1 order by col2 nulls last) col3
  9  from data
 10  /
      COL1       COL2       COL3
---------- ---------- ----------
         1          2
         1          3          2
         1                     3
         2          5
         2          6          5
         2                     6

6 rows selected.

Regards
Michel
Re: Problem with LAG function [message #389530 is a reply to message #389528] Mon, 02 March 2009 08:38 Go to previous messageGo to next message
Buchas
Messages: 83
Registered: March 2006
Member
Now this is really amasing.
Thank you Michel !
Re: Problem with LAG function [message #389531 is a reply to message #389528] Mon, 02 March 2009 08:48 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, you can add another column in to hold the LEAD of col2, add a row generator, and then just pick which rows you display:
CREATE TABLE test_126
(COL1 NUMBER,
COL2 NUMBER
);

INSERT INTO TEST_126 (COL1, COL2) VALUES (1,2);
INSERT INTO TEST_126 (COL1, COL2) VALUES (1,3);
INSERT INTO TEST_126 (COL1, COL2) VALUES (2,5);
INSERT INTO TEST_126 (COL1, COL2) VALUES (2,6);
COMMIT;

SELECT distinct
       COL1
      ,case when src = 1 then COL2
            when src=  2 then lead(col2) over (partition by col1 order by col1,col2) 
            end  as col2
      ,case when src = 1 then LAG(COL2)  OVER (partition by col1 ORDER BY COL1,col2)
            when src = 2 then col2
            end  as col3
FROM test_126
    ,(select level src from dual connect by level <=2) x
order by col1,col2;


It feels like you should be able to do something with a Hierarchical query, but I've not been able to make that work.
Previous Topic: Sorting strings with numbers
Next Topic: Problem with DBMS_JOB
Goto Forum:
  


Current Time: Fri Dec 09 21:03:12 CST 2016

Total time taken to generate the page: 0.08324 seconds