Recursive ordering

Database
Teradata Employee

Recursive ordering

Hello,

Please help me on this issue. Generally i want my recursice query to do the recursion by ordered. I want permutations from 2 column. See below.

1-Have this data

content_type, product_offered

0    2

0    5

0    4

0    6

0    1

0    3

2-I want to have all permutation in 1 row. Example:

0    1

0    1,2

0    1,2,3

0    1,3

etc...

I have the code to create the permutation but it is creating in bad order. Example:

0   3,2,1

0   2,1

This should be ordered!

What i use is:

WITH RECURSIVE News_Table (content_type, product_offered,LVL) AS

    (

      SELECT content_type,  product_offered(VARCHAR(8000)) as product_offered,1

      FROM our_table

   WHERE content_type = 0

QUALIFY RANK() OVER(PARTITION BY product_offered ORDER BY product_offered DESC) = 1

      UNION ALL

      SELECT C1.content_type, trim(C1.product_offered) || ',' || trim(C2.product_offered), lvl+1

      FROM  

      our_table 

      as C1

      inner join News_table as C2 on C1.content_type = C2.content_type and C1.product_offered>C2.product_offered

      WHERE C1.content_type = 0

)

SELECT content_type, product_offered,LVL FROM News_Table

I really appreciate your help.

Best Regard,

Zsolt

1 REPLY
N/A

Re: Recursive ordering

Hi Zsolt,

seems you only need to switch C1 and C2 :-)

...
UNION ALL

SELECT C1.content_type, trim(C2.product_offered) || ',' || trim(C1.product_offered), lvl+1
...