Partition clause with Reset option

Database

Partition clause with Reset option

Hello all,

This is my first post in the forum and please excuse me if i am off the hook with my post.

I am writing a sql logic and i am stuck at a point where i am not sure how to proceed.  I am hereby giving some synopsis about the issue and i will really appreciate if you can guide me how to proceed to the resolution.

I have a table in which i am partitioning the data based on few columns. After applying PARTITION clause on String1, String2 and applying ORDER BY on Timestamp, say the data looks as below.

TransactionID    String1   String2   Flag1   Timestamp

111                   XXX         YYY       0          2015-02-21 18:56:41  

112                   XXX         YYY       0          2015-02-21 18:56:43

113                   XXX         YYY       1          2015-02-21 18:56:45

114                   XXX         YYY       0          2015-02-21 18:56:48

115                   XXX         YYY       0          2015-02-21 18:56:50

116                   XXX         YYY       1          2015-02-21 18:56:55

In the above data, i want to link TransactionID 113 to 111 and similary 116 to 114 based on the Flag1 column. Basically the transactionID with Flag1 = 1 need to be mapped to the transactionID of first record with Flag1 = 0 from its previous record set.

As a first step(once i have this derived column then i can have an other step to link to do a final linking) to achieve this , i tried to do as below,

Select 

MAX(transactionID)

Over (PARTITION BY String1, String2  ORDER BY Timestamp

         RESET WHEN Flag1 = 1 ROWS UNBOUNDED PRECEDING

        ) As derived_column

, TransactionID , String1, String2, Flag1, Timestamp

FROM DataTable;

Actual Resultset is as below:

derived_column  TransactionID    String1   String2   Flag1   Timestamp

-111                    -111                   XXX         YYY       0          2015-02-21 18:56:41  

-111                    -112                   XXX         YYY       0          2015-02-21 18:56:43

 113                     113                   XXX         YYY       1          2015-02-21 18:56:45

-114                    -114                   XXX         YYY       0          2015-02-21 18:56:48

-114                    -115                   XXX         YYY       0          2015-02-21 18:56:50

 116                     116                   XXX         YYY       1          2015-02-21 18:56:55

Expected Resultset is as below:

derived_column  TransactionID    String1   String2   Flag1   Timestamp

-111                    -111                   XXX         YYY       0          2015-02-21 18:56:41  

-111                    -112                   XXX         YYY       0          2015-02-21 18:56:43

 113                     113                   XXX         YYY       1          2015-02-21 18:56:45

 113                    -114                   XXX         YYY       0          2015-02-21 18:56:48

 113                    -115                   XXX         YYY       0          2015-02-21 18:56:50

 116                     116                   XXX         YYY       1          2015-02-21 18:56:55

So if you notice the resultset, i am able to get the first 3 records correct, but the 4th, 5th, 6th records derived_column is wrong. I am expecting to be -114, -114, 116 in the same order instead of 113,113,116.

Please help me get to my expected result set. 

Thanks in advance!

Tags (2)
1 REPLY

Re: Partition clause with Reset option

Kindly note that the Actual Resultset and Expected Resultset in my initial post are interchanged wrongly. I am sorry if i have caused confusion there.