Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unhappy Unanswered: Query Help - Combining and comparing two rows.

    Hello,
    I know that this title could be little misleading. But, i am looking for some help in this case of mine.

    CREATE TABLE "MY_TABLE"
    ("NBR" VARCHAR2(20 BYTE),
    "MO_YR" NUMBER,
    "EFF_DT" DATE
    ) ;

    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('101',201310,Sysdate-20);
    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('102',201310,Sysdate-15);
    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('103',201310,Sysdate-10);
    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('104',201310,Sysdate-5);
    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('105',201310,Sysdate);
    COMMIT;
    SELECT * FROM MY_TABLE;

    Here, i need to get a dataset like this.

    Prev. | Old | Eff DT
    105 104 Sysdate
    104 103 Sysdate-5
    103 102 Sysdate-10
    102 101 Sysdate-15

    As you can see, i need to merge the two consecutive records in the above fashion. Thanks in advance.

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Hi, thanks for the test case (a working one too, fab!)

    What's the difference between previous and old?

    Take a look at the LAG Function it is designed to allow you to pull previous rows without self joins. If it doesn;t help, please give us a little more detail on how you arrive at your required results.

  3. #3
    Join Date
    Oct 2013
    Posts
    2

    Post

    Quote Originally Posted by pablolee View Post
    Hi, thanks for the test case (a working one too, fab!)

    What's the difference between previous and old?

    Take a look at the LAG Function it is designed to allow you to pull previous rows without self joins. If it doesn;t help, please give us a little more detail on how you arrive at your required results.
    Thanks. The LAG function helped.

    NBR AS CURR_NBR,
    LAG(NBR, 1, 0) OVER (PARTITION BY MO_YR
    ORDER BY MO_YR, EFF_DT) AS PREV_NBR.

    Please mark your post as answer.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Glad you got it sorted
    Please mark your post as answer.[sic]
    but I'm not sure why I would mark my post as answered, it wasn't me that asked a question...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •