Query Help - Merging two rows

Hi,

PFB the sample data.

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.

.
Try using the one of these analytic functions: LAG or LEAD.

Perhaps you could learn about analytic functions if you Read This Fine Manual:
Oracle® Database SQL Language Reference

Quote:

---

Originally Posted by LKBrwn_DBA

.
Try using the one of these analytic functions: LAG or LEAD.

Perhaps you could learn about analytic functions if you Read This Fine Manual:
Oracle® Database SQL Language Reference

---

Thank you Good sir,
I know most of these functions and use them often. But, this requirement of mine is lil twisted and i just can not figure out how to do it. Anyways, thanks for writing in.

Hoping for some help on this.

-Parthiban

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

Thanks :)

Sorry i clicked report button by mistake.

Quote:

---

Originally Posted by ParthibanSekar

Sorry i clicked report button by mistake.

---

I'm not sure if that feature even works... :)

Yep, you got it:

Code:

---

SQL> WITH my_table (nbr, mo_yr, eff_dt)
  2    AS (SELECT '101', 201310, TRUNC ( SYSDATE - 20) FROM DUAL UNION ALL
  3        SELECT '102', 201310, TRUNC ( SYSDATE - 15) FROM DUAL UNION ALL
  4        SELECT '103', 201310, TRUNC ( SYSDATE - 10) FROM DUAL UNION ALL
  5        SELECT '104', 201310, TRUNC ( SYSDATE -  5) FROM DUAL UNION ALL
  6        SELECT '105', 201310, TRUNC ( SYSDATE    ) FROM DUAL)
  7  SELECT nbr AS curr_nbr
  8      , LAG ( nbr, 1, 0)
  9          OVER ( PARTITION BY mo_yr ORDER BY mo_yr, eff_dt) AS prev_nbr
 10      , eff_dt
 11    FROM my_table
 12*  ORDER BY eff_dt desc
SQL> /

CURR_NBR  PREV_NBR  EFF_DT
--------- --------- -----------------------------
105      104      28-Oct-2013 00:00:00
104      103      23-Oct-2013 00:00:00
103      102      18-Oct-2013 00:00:00
102      101      13-Oct-2013 00:00:00
101        0      08-Oct-2013 00:00:00

SQL>

---

;)