I want query to sort based on two amount columns in zigzag order. My source looks like below.
My query result should be as below along with newly derived column (amt_seq)
Looks like you simply need to order by created_ts and add a ROW_NUMBER() OVER (ORDER BY created_ts) AS amt_seq.
Otherwise you need a recusrive query, but how is the row with amt_seq 8 calculated?
This example might have created_ts in order. But ideal scenario is that, created_ts is not valid sort order. I need a query to sort based on old_amt and new_amt in zigzag order.
Reg amt_seq 8, Since the new_amt -30682 is not present as part of old_amt itself, it picks a random one based on the order I have provided. Pls skip that.
Ideally first record should be min(created_ts) and from there, new_amt of the first record should be old_amt of next record and so on.
How to overcome duplicates in recursive i.e. whenever it encounters duplicate values, it goes in infinite loop.