Htykuut

In general, I have a Key (Contract) that changes to another key in a row.
The changed Key can be changed and for that we have another row.
The key go back as it was at the beginning.
I need a row for the first Key (where it all started) and the newest Key (that is skipping all the other keys that were in between).

This creates the table:

CREATE TABLE CONTRACT ("NAME" VARCHAR2(20 BYTE)

                                   ,"OLD_CONTRACT" VARCHAR2(20 BYTE)

                                   ,"NEW_CONTRACT" VARCHAR2(20 BYTE)

                                   ,"NEW_CONTRACT_DATE" NUMBER(10)) ;



COMMIT;



INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)

VALUES ('John','1','10',20180101);

INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)

VALUES ('Ronnie','10','6',20180107);

INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE) 

VALUES ('Kim','6','1',20180128);

INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE) 

VALUES ('Nathaly','3','2',20180419);

INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)

VALUES ('Gorge','2','8',20180713);

INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE) 

VALUES ('Allen','8','20',20180921);

INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE) 

VALUES ('Tom','4','11',20170103);



COMMIT;

This is how it looks

Name    old_key      new Key    Updated date 

----    -------      -------    ------------ 

John      1             10      20180101

Ronnie    10             6      20180107

Kim        6             1      20180128

Nathaly    3             2      20180419

Gorge      2             8      20180713

Allen      8             20     20180921

Tom        4             11     20170103

First 3 rows returns only ONE ROW.

Old Key  New Key

-------  -------

   1        1

Why? Because

1   Becomes 10

10  Becomes 6

6   Becomes 1   

Next 3 rows returns only ONE ROW.

Old Key  New Key

-------  -------

   3        20

Why? Because

3   Becomes 2

2   Becomes 8

8   Becomes 20

Last row returns ONE ROW.

Old Key  New Key

-------  -------

   4        11

Why? Because

4   Becomes 11

I need help writing Query for this scenario

Here is a query using CONNECT BY that does the connection that you want and need.
From its results you have to select the first old_contract and the last new_contract.

SELECT NAME,OLD_CONTRACT,NEW_CONTRACT, LEVEL, rownum 

FROM contract

START WITH OLD_CONTRACT = 3

CONNECT BY NOCYCLE PRIOR NEW_CONTRACT = OLD_CONTRACT

Results

NAME    OLD_CONTRACT    NEW_CONTRACT    LEVEL   ROWNUM

Nathaly 3               2               1       1

Gorge   2               8               2       2

Allen   8               20              3       3