5 Replies Latest reply: Feb 4, 2013 2:18 PM by Frank Kulash RSS

    produce a range of numbers with select from the gaps of ID column

    PuraVidaOTN
      (1)

      Suppose I have a table t which has rows like this:

      A B
      ---------- ----------
      2 4
      6 7

      I would like to use select to produce rows with numbers ranging between A column and B column inclusive
      for each row, like this:

      select ... from t ...

      2
      3
      4
      6
      7

      (2)

      Suppose I have ID column which has gaps, I would like to get lowest <N> available numbers between the gaps.
      I did research, and I can find the range of the gaps, but I cannot have the numbers listed individually, that is why
      I ask question (1). But is there a direct way to get the list instead of going through ranges and the list.

      For example, I have ID column which has

      2
      5
      6
      7
      9
      2000000

      I would like to get a select query that produces

      select ...

      3
      4
      8
      10
      11

      I have a way to get a list from 2 to 2000000 and then minus what we have to get all the
      gap numbers, but that is not efficient and could runs out of memory.

      PS: Before I post to this group, I did research already on "connect by", with recursive queries.
        • 1. Re: produce a range of numbers with select from the gaps of ID column
          jeneesh
          PuraVidaOTN wrote:
          I would like to use select to produce rows with numbers ranging between A column and B column inclusive
          for each row, like this:
          Assuming no duplicates in the table..
          with t as
          (
           select 2 c1,4 c2 from dual union all
           select 6 c1,7 c2 from dual 
          )
          select c1,c2,c1+level-1 c
          from t
          connect by c1+level-1 <= c2
          and prior c1 = c1
          and prior c2 = c2
          and prior sys_guid() is not null;
          
          C1 C2 C
          -- -- -
           2  4 2 
           2  4 3 
           2  4 4 
           6  7 6 
           6  7 7 
          Suppose I have ID column which has gaps, I would like to get lowest <N> available numbers between the gaps.
          I did research, and I can find the range of the gaps, but I cannot have the numbers listed individually, that is why
          I ask question (1). But is there a direct way to get the list instead of going through ranges and the list.

          I have a way to get a list from 2 to 2000000 and then minus what we have to get all the
          gap numbers, but that is not efficient and could runs out of memory.
          That is the best possible way in my opinion..

          Or instead minus, you can try outer join..

          Edited by: jeneesh on Dec 19, 2012 9:28 PM
          • 2. Re: produce a range of numbers with select from the gaps of ID column
            Frank Kulash
            Hi,
            PuraVidaOTN wrote:
            (1)

            Suppose I have a table t which has rows like this:

            A B
            ---------- ----------
            2 4
            6 7

            I would like to use select to produce rows with numbers ranging between A column and B column inclusive
            for each row, like this:

            select ... from t ...

            2
            3
            4
            6
            7
            See the answer to (2) below. The sub-query got_n is just generating a "table" like the one above. If you already have such a table, use it instead of got_n.
            (2)

            Suppose I have ID column which has gaps, I would like to get lowest <N> available numbers between the gaps.
            See note at the end.
            I did research, and I can find the range of the gaps, but I cannot have the numbers listed individually, that is why
            I ask question (1). But is there a direct way to get the list instead of going through ranges and the list.

            For example, I have ID column which has

            2
            5
            6
            7
            9
            2000000

            I would like to get a select query that produces

            select ...

            3
            4
            8
            10
            11

            I have a way to get a list from 2 to 2000000 and then minus what we have to get all the
            gap numbers, but that is not efficient and could runs out of memory.
            Post your code. It's hard to say what you're doing wrong when we don't know what you're doing.
            PS: Before I post to this group, I did research already on "connect by", with recursive queries.
            Here's one way:
            WITH     got_n     AS
            (
                 SELECT  id
                 ,     LEAD (id) OVER (ORDER BY id)
                          - id       AS n
                 FROM    table_x
            )
            ,     cntr     AS
            (
                 SELECT     LEVEL     AS n
                 FROM     (
                           SELECT  MAX (n)     AS max_n
                           FROM     got_n
                      )
                 CONNECT BY     LEVEL     <= max_n
            )
            SELECT    g.id + c.n     AS missing_id
            FROM       got_n  g
            JOIN       cntr      c  ON  c.n  <= g.n
            WHERE       g.n      > 1
            ORDER BY  missing_id
            ;
            If you'd care to post CREATE TABLE and INSERT statements for your smaple data, and the results you want from that data, then I could test this.

            Edited by: Frank Kulash on Dec 19, 2012 11:02 AM

            I'm not sure what you mean by "get lowest <N> available numbers ".
            I think you're saying that there may be millions of missing numbers, but you only want a given number, say 1000, of them (the lowest 1000). If that's what you want, then the query above can be modified like this:
            VARIABLE  new_id_cnt     NUMBER
            EXEC     :new_id_cnt := 1000;
            
            
            WITH     got_n     AS
            (
                 SELECT  id
                 ,     LEAD (id) OVER (ORDER BY id)
                          - id       AS n
                 FROM    table_x
            )
            ,     cntr     AS
            (
                 SELECT     LEVEL     AS n
                 FROM     (
                           SELECT  MAX (n)     AS max_n
                           FROM     got_n
                      )
                 CONNECT BY     LEVEL     <= LEAST (max_n, :new_id_cnt)
            )
            ,     got_r_num     AS
            (
                 SELECT    g.id + c.n     AS missing_id
                 FROM       got_n  g
                 JOIN       cntr      c  ON  c.n  <= g.n
                 WHERE       g.n      > 1
                 ORDER BY  missing_id
            )
            SELECT     missing_id
            FROM     got_r_num
            WHERE     ROWNUM     <= :new_id_cnt
            ;
            • 3. Re: produce a range of numbers with select from the gaps of ID column
              PuraVidaOTN
              First of all, I would like to thank jeneesh and Frank for helpful and correct reply. However, due to the a bug of this forum,
              when I mark jeneesh's reply as correct, I cannot mark Frank's reply as correct anymore. Therefore I would like to formally
              state here that I have tested both solutions and they are both correct. jeneesh and Frank use different approach, connect by
              and less join ( <=, I do not know what is the proper name for it ).

              Secondly I would like to report my small findings: use connect by, you control the level from 1 to the max number, so you
              do not need to use order by, and the performance is better (0.37 seconds version 0.92 seconds). And also it performs better
              if I use an intermediate view to limit the result sets. One line of code is worth one thousand words, now I am posting two
              versions of the code and highlighted the points I mentioned here.

              I am sorry that either I do not know how to format the code or I do not have the capability when posting on this forum.
              The code listing does not look good, but I hope you get the idea. I have "plain text" and I am not if I can use fixed width font
              and preserve space. After I used the spelling checker, it becomes double spaced and I have to manually delete the blank lines.
              /* I learned about { code } after the post and edited the original post */

              Thanks again to both jeneesh and Frank.

              "connect by" version:
              SQL> VARIABLE  new_id_cnt NUMBER
              SQL> EXEC        :new_id_cnt := 10;
              
              PL/SQL procedure successfully completed.
              Elapsed: 00:00:00.00
              
              SQL> WITH my_table AS
                2  (
                3     SELECT num
                4     FROM   prs_elm
                5     WHERE  1=1
                6  )
                7  ,       my_num   AS
                8  (
                9  SELECT num, ct
               10  FROM   (
               11                    SELECT num AS num, LEAD(num) OVER (ORDER BY num) - num - 1 AS ct
               12                    FROM   ( SELECT 0 AS num
               13                             FROM   DUAL
               14                             UNION ALL
               15                             SELECT num
               16                             FROM   my_table
               17                             UNION ALL
               18                             SELECT ( MAX(num) + :new_id_cnt + 1 ) AS num
               19                             FROM   my_table
               20                    )
               21          )
               22  WHERE      ct >= 1
               23  AND         ROWNUM <= :new_id_cnt
               24  )
               25  SELECT     num + level AS available_id
               26  FROM       my_num
               27  WHERE      ROWNUM  <= :new_id_cnt
               28  CONNECT BY level <= ct
               29  AND        prior num = num
               30  AND        prior sys_guid() is not null
               31  ;
              
              AVAILABLE_ID
              ------------
                      3219
                      3261
                      3264
                      3269
                      3270
                      3275
                      3281
                      3288
                      3289
                      3290
              
              10 rows selected.
              Elapsed: 00:00:00.37
              "Less join" version:
              SQL> VARIABLE  new_id_cnt NUMBER
              SQL> EXEC        :new_id_cnt := 10;
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:00.00 
              
              SQL> WITH my_table AS
                2  (
                3     SELECT num
                4     FROM   prs_elm
                5     WHERE  1=1
                6  )
                7  ,       my_num   AS
                8  (
                9                    SELECT num AS num, LEAD(num) OVER (ORDER BY num) - num - 1 AS ct
               10                    FROM   ( SELECT 0 AS num
               11                             FROM   DUAL
               12                             UNION ALL
               13                             SELECT num
               14                             FROM   my_table
               15                             UNION ALL
               16                             SELECT ( MAX(num) + :new_id_cnt + 1 ) AS num
               17                             FROM   my_table
               18                    )
               19  )
               20  ,       my_cnt    AS
               21  (
               22          SELECT  LEVEL AS ct
               23          FROM    DUAL
               24          CONNECT BY    LEVEL <= :new_id_cnt
               25  )
               26  SELECT     available_id
               27  FROM
               28  (
               29          SELECT    n.num + c.ct AS available_id
               30          FROM      my_num n
               31          JOIN      my_cnt c  ON c.ct <= n.ct
               32          WHERE     n.ct >= 1
               33          ORDER BY  available_id
               34  )
               35  WHERE      ROWNUM  <= :new_id_cnt
               36  ;
              
              AVAILABLE_ID
              ------------
                      3219
                      3261
                      3264
                      3269
                      3270
                      3275
                      3281
                      3288
                      3289
                      3290
              
              10 rows selected.
              Elapsed: 00:00:00.92
              PS: In Frank's code, there is a typo, <= should read as <.

              Edited by: PuraVidaOTN on 04-feb-2013 22:49
              What: To use
               tags to format the code.
              
              Edited by: PuraVidaOTN on 04-feb-2013 22:56                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
              • 4. Re: produce a range of numbers with select from the gaps of ID column
                sb92075
                How do I ask a question on the forums?
                SQL and PL/SQL FAQ


                scroll down #9 to learn how to use
                 tags!                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: produce a range of numbers with select from the gaps of ID column
                  Frank Kulash
                  Hi,
                  PuraVidaOTN wrote:
                  First of all, I would like to thank jeneesh and Frank for helpful and correct reply. However, due to the a bug of this forum,
                  when I mark jeneesh's reply as correct, I cannot mark Frank's reply as correct anymore.
                  That's how this forum is designed: you can only mark 1 reply as "Correct".
                  You did the right thing: if 2 (or more) replies are equally good, mark the 1st one a "Correct" and the other(s) as "Helpful".
                  I am sorry that either I do not know how to format the code or I do not have the capability when posting on this forum.
                  To post in a fixed-width font and keep this site from removing extra spaces, type these 6 characters

                  \
                  (small letters only, inside curly brackets) before and after each section of formatted text.
                  See the forum FAQ {message:id=9360002}