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

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

(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
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
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
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
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
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}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        ``````