sorting data in oracle

Question

I have a tricky scenario.

Data in the table is as follows

CustomerId  Transaction Type    Transaction Amount
1               Payment          100
1               payment          200
1               ReversePayment  -100
1               ReversePayment  -200

I have transnational table with transaction types being "Payment", "ReversePayment". There are multiple records for a customer with some records being Payment and some being ReversePayment.

Is there a way to sort data as follows

CustomerId  Transaction Type    Transaction Amount
1               Payment              100
1               ReversePayment      -100
1               payment              200
1               ReversePayment      -200

If any one have a solution, please help.

You still didn't tell us what logic must be followed, to arrive at that ordering (regardless of whether or how that can be done in code). We can take a guess: you want to show "matching" payment and reverse payment together, on consecutive lines. Is that it? If so - will a reverse payment always match a payment (and, perhaps, there may be some "payments" that were not reversed, and you need those at the end? You need to clarify that too.) Is it not possible, for example, to have payments of 100 and 200, and a **single** reverse payment of -300? — , Feb 13 '20 at 21:36
If it's the latter, and you need to see what payment or payment**s** is or **are** reversed by a single ReversePayment, then (1) the problem no longer has a unique solution in general, and (2) the problem is much harder. — , Feb 13 '20 at 21:36
Hi mathguy,yes you are correct. my requirement is to show "matching" payment and reverse payment together, on consecutive lines — Nazeer Syed, Feb 13 '20 at 22:20

score 2 · Answer 1 · answered Feb 13 '20 at 21:34

2

For this dataset, this should do it:

order by
    customerId,
    abs(transaction_amount),
    transaction_amount desc

This sort does not worry about the transaction type, but only about the amounts. The criteria on the absolute value of the amount ties records that have opposite values, then the next criteria puts the positive value first.

answered Feb 13 '20 at 21:34

GMB

216,147
25
84
135

The OP still needs to fully explain the logic; but if it's to show each reverse payment under the payment it reverses, then your solution will not give the desired result - specifically when there are payments (and corresponding reversals) of equal amounts. For example: two payments of 100 and two reversals of -100. Your solution will show 100, 100, -100, -100, when in fact the desired result is 100, -100, 100, -100 (if in fact that's the desired result). – Feb 13 '20 at 21:38
1

By the way, I see the answer already has (as I am writing this) two upvotes and one downvote. None of those is from me. The downvoter, in particular, has not explained their downvote. – Feb 13 '20 at 21:39
@mathguy: agreed. I clearly mentionned that this works *for this dataset*. I understand what you say: for this to work, we would need an additional column that somehow defines the order (or the group) of payments... which is not showing in the sample data. – GMB Feb 13 '20 at 21:41
1

Or we can create our own such column, with ROW_NUMBER for example. But I won't speculate (much less write a solution) until the OP clarifies the requirement. No point in solving a problem the OP may not even have. – Feb 13 '20 at 21:42
Hi GMB, your answer works perfectly for 90% of data. remaining 10% of data has identical amounts like belowCustomerId Transaction Type Transaction Amount 1 Payment 100 1 payment 100 1 ReversePayment -100 1 ReversePayment -100 i want output like CustomerId Transaction Type Transaction Amount 1 Payment 100 1 ReversePayment -100 1 payment 100 1 ReversePayment -100 – Nazeer Syed Feb 13 '20 at 22:15

score 1 · Answer 2 · answered Feb 13 '20 at 23:25

The question is unclear, but my interpretation is that the OP wants to interleave the payments and reverses. This suggests window functions:

order by customerid,
         abs(transactionamount),
         row_number() over (partition by customerid, transactiontype, abs(transactionamout) order by customerid),
         transactiontype asc

sorting data in oracle

2 Answers2

Linked