3 Replies Latest reply on Dec 27, 2012 10:37 AM by jeneesh

# sys_connect_by_path

pickup 8 random number from 1-30
and output
like 31 33 26 14 22 30 5 4
`````` SELECT sys_connect_by_path(ID,' ') FROM (
SELECT trunc(dbms_random.VALUE(1,30))+LEVEL-1 ID , ROWNUM rn FROM dual CONNECT BY LEVEL<=8)
connect by rn=prior rn-1

33
33 11
33 11 14
33 11 14 29
33 11 14 29 23
33 11 14 29 23 12
33 11 14 29 23 12 17
33 11 14 29 23 12 17 7

how to walk though the tree : from 33 to 7 without use the below one

use max i can get the right output

SELECT max(sys_connect_by_path(ID,' ')) FROM (
SELECT trunc(dbms_random.VALUE(1,30))+LEVEL-1 ID , ROWNUM rn FROM dual CONNECT BY LEVEL<=8)
connect by rn=prior rn-1
``````
• ###### 1. Re: sys_connect_by_path
like 31 33 26 14 22 30 5 4
Given that 31 and 33 are not 'from 1-30' I'm not sure I fully understand your requirement.

Are you trying to list 8 different integers betwen 1 and 30?
without use the below one
Which bit? The MAX?
• ###### 2. Re: sys_connect_by_path
``````select id from (
SELECT sys_connect_by_path(ID,' ') id,row_number()over(order by level desc )rn FROM (
SELECT trunc(dbms_random.VALUE(1,30))+LEVEL-1 ID , ROWNUM rn FROM dual CONNECT BY LEVEL<=8)
connect by rn=prior rn-1
) where rn=1``````
• ###### 3. Re: sys_connect_by_path
If you want unique random numbers between 1 and 31 (Actually random numbers cannot be "UNIQUE")
``````with t as
(
select level lvl,dbms_random.value(0,1) rnd
from dual
connect by level <= 30
order by rnd
)
select listagg(lvl,' ') within group(order by rnd) rnd
from t
where rownum <= 8;

RND
--------------------------
5 1 14 9 22 30 19 10``````