The Serial num is a unique number and a primary key in the table and its tied to a specific order num .
What i have done so far :
WITH RECURSIVE New_Table (ord_nbr, Serial_Num,LVL) AS ( SELECT ord_nbr, min (Serial_Num(VARCHAR(8000))) as Serial_Num,1 FROM Service_table WHERE ord_nbr = '1111' group by 1
SELECT C2.ord_nbr, trim(C1.Serial_Num) || ',' || trim(C2.Serial_Num), lvl+1 FROM Service_table as C1 inner join New_table as C2 on C1.ord_nbr = C2.ord_nbr and C1.Serial_Num>C2.Serial_Num WHERE C1.ord_nbr = '1111' )
SELECT ord_nbr, Serial_Num,LVL FROM New_Table QUALIFY RANK() OVER(PARTITION BY ord_nbr order by Serial_Num desc ) = 1
This does the job but ... it pairs one serial number to another and runs all possible combinations till it reaches the last . so for orders that could have 100 or 200 serial numbers you quickly run out of spool space . I tried a lot of combinations without any success .. i would be grateful for ideas .