3 Replies Latest reply: Dec 27, 2012 4:37 AM by jeneesh RSS

    sys_connect_by_path

    969801
      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)
       START WITH rn=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)
       START WITH rn=8
       connect by rn=prior rn-1
       
        • 1. Re: sys_connect_by_path
          padders
          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
            981855
            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)
             START WITH rn=8
             connect by rn=prior rn-1
             ) where rn=1
            • 3. Re: sys_connect_by_path
              jeneesh
              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