select parent child account from row_number using CASE WHEN (SQL)-CodePudding

I would like to use CASE WHEN to select ORDER_NUMBER from that same group of id0, Which the pairing conditions are as follows:

A pair with B (A is the main type order. B is the minor type order) and B pairs with C

X pair with Y (X is the main type order. Y is the minor type order) and Y pairs with Z

id0   id1     ORDER_NUMBER       ORDER_NUMBER_2    Type    RN
--- -------- ----------------- ----------------- --------- ----
47   48      2000036                 0              A       1
47   49      300026660000            0              B       2
47   49      300026660001        300026660000       C       3
250  251     2000341                 0              X       1
250  252     300000193000            0              Y       2
250  252     300000193001        300000193000       Z       3

Expected result:

   Type_main| Type_minor| ORDER_NUMBER_main  | ORDER_NUMBER_minor
------------ ---------- --------------------- ----------------------
    A      |    B      | 2000036             |  300026660000
    B      |    C      | 300026660000        |  300026660001
    X      |    Y      | 2000341             |  300000193000
    Y      |    Z      | 300000193000        |  300000193001

CodePudding user response：

With a join to the table itself, you can easily achieve the desired result

SELECT T.Type_main,T.Type_minor,T.ORDER_NUMBER_main,T.ORDER_NUMBER_minor
FROM
     (SELECT 
        t1.Type AS Type_main,
        t2.Type AS Type_minor,
        t1.ORDER_NUMBER AS ORDER_NUMBER_main,
        t2.ORDER_NUMBER AS ORDER_NUMBER_minor,
     ROW_NUMBER() OVER(PARTITION BY t1.Type ORDER BY t1.id0) seq
     FROM myTable t1
     JOIN
     myTable t2 ON t1.id0 = t2.id0 and t1.RN < t2.RN) T
WHERE T.seq = 1

demo in db<>fiddle