Sorting based on two columns in zigzag order

Database
Enthusiast

Sorting based on two columns in zigzag order

Hi All,

I want query to sort based on two amount columns in zigzag order. My source looks like below.

idcreated_tsold_amtnew_amt
12015-09-03 07:16:359487-36514
22015-09-03 01:04:32-8109-6469
32015-09-03 06:27:40-20044-36514
42015-09-02 17:11:4220203-8109
52016-02-14 10:44:2642967460-38428
62016-02-14 11:09:13-38428-44544
72015-09-03 07:16:34-365149487
82015-09-03 10:11:41-36514-30682
92015-09-03 05:24:23-6469-20044

 

My query result should be as below along with newly derived column (amt_seq)

idcreated_tsold_amtnew_amtamt_seq
42015-09-02 17:11:4220203-81091
22015-09-03 01:04:32-8109-64692
92015-09-03 05:24:23-6469-200443
32015-09-03 06:27:40-20044-365144
72015-09-03 07:16:34-3651494875
12015-09-03 07:16:359487-365146
82015-09-03 10:11:41-36514-306827
52016-02-14 10:44:2642967460-384288
62016-02-14 11:09:13-38428-445449

 

 

5 REPLIES
Junior Contributor

Re: Sorting based on two columns in zigzag order

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?

Enthusiast

Re: Sorting based on two columns in zigzag order

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.

Enthusiast

Re: Sorting based on two columns in zigzag order

u mean to say first data should be ordered by new_amt and then by old_amt like .. order by new_amt,old_amt. Am I correct?
Enthusiast

Re: Sorting based on two columns in zigzag order

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.

Enthusiast

Re: Sorting based on two columns in zigzag order

Hi @dnoeth,

How to overcome duplicates in recursive i.e. whenever it encounters duplicate values, it goes in infinite loop.