Home » SQL & PL/SQL » SQL & PL/SQL » Missing number in series of numbers (Oracle 11g, windows)
Missing number in series of numbers [message #651463] Wed, 18 May 2016 06:58 Go to next message
Vijay55
Messages: 16
Registered: October 2015
Junior Member
Hi All,

I am about find missing numbers in a series of numbers. I have written below query and it is working fine.
CREATE TABLE SEQUENCE
   (	SNO NUMBER
   );
   
insert into sequence(sno) values(1);   
insert into sequence(sno) values(2);
insert into sequence(sno) values(4);
insert into sequence(sno) values(5);
insert into sequence(sno) values(7);
insert into sequence(sno) values(10);

SEQUENCE
-------
1
2
4
5
7
10

 SELECT (min_sno - 1 + level) missing_number
FROM ( SELECT MIN(sno) min_sno
, MAX(sno) max_sno
 FROM sequence
  )
 CONNECT BY level <= max_sno - min_sno + 1
  minus
 SELECT sno
 FROM sequence  ;
 
 Result is:
 missing_number
 --------------
 3
 6
 8
 9



Now i'm handling NVL function on top of this query to get maximum number in the series suppose if there is no missing number present in the series.

adding all missed number into the table. now table data will be like
SEQUENCE
-------
1
2
3
4
5
6
7
8
9
10


My below Query is not returning any value.


SELECT nvl((min_sno - 1 + level),max_sno) as missing_number
FROM ( SELECT MIN(sno) min_sno
, MAX(sno) max_sno
 FROM sequence
  )
 CONNECT BY level <= max_sno - min_sno + 1
  minus
 SELECT sno
 FROM sequence  ;



I would like know the reason why NVL condition is not working here and how to bring maximum value suppose no missing number in series.

Thanks in advance for your help!!

Thanks,
Vijay
Re: Missing number in series of numbers [message #651464 is a reply to message #651463] Wed, 18 May 2016 07:25 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>I would like know the reason why NVL condition is not working here
When will MIN_SNO ever be NULL?
Re: Missing number in series of numbers [message #651465 is a reply to message #651463] Wed, 18 May 2016 07:40 Go to previous messageGo to next message
flyboy
Messages: 1886
Registered: November 2006
Senior Member
Hi,

Maybe you should realize the difference between the unknown value (NULL) in the row and no value at all (zero rows). NVL function works as expected. But, there is no row to be applied on.

You can "generate" the "NULL" row e.g. by additional "UNION ALL SELECT NULL FROM DUAL". The question is: why do you want to return it in one query? Why do you not want to return it in the query on the first data set? Looks like a quite confusing requirement.

If you wanted to also return the "missing" number greater than the MAX value, increasing the 1 to 2 in the CONNECT BY clause will easily do that without the involvement of NVL or any other obfuscation.
Re: Missing number in series of numbers [message #651502 is a reply to message #651465] Thu, 19 May 2016 04:24 Go to previous messageGo to next message
Vijay55
Messages: 16
Registered: October 2015
Junior Member
Hi Both.. Thank you for your input. I got the difference between NULL value and empty value.

@Flyboy: basically my requirement is, i want to find out first missing number from a series of numbers, also if there is no missing number in the series then i want to get maximum number +1 of the series.

I have achieved by below query.

with t as
(
select count(0) count_value
 from (
 SELECT (((select case when min_sno>1 then 1 else min_sno end from dual)) - 1 + level) missing_number
FROM ( SELECT MIN(sno) min_sno
, MAX(sno) max_sno
 FROM sequence
  )
 CONNECT BY level <= (max_sno) - (select case when min_sno>1 then 1 else min_sno end from dual) +1
  minus
 SELECT sno
 FROM sequence  
 
 ) 
 )
 select   case when (t.count_value)>=1 then  
 
 ( select missing_number from
 (
SELECT (((select case when min_sno>1 then 1 else min_sno end from dual)) - 1 + level) missing_number
FROM ( SELECT MIN(sno) min_sno
, MAX(sno) max_sno
 FROM sequence
  )
 CONNECT BY level <= (max_sno) - (select case when min_sno>1 then 1 else min_sno end from dual) +1
  minus
 SELECT sno
 FROM sequence 
 )
 where rownum=1
)
else  (select max(sno)+1 from sequence)  end as next_val
 from
 t;

 


Thank you Both:)
Re: Missing number in series of numbers [message #651504 is a reply to message #651502] Thu, 19 May 2016 05:04 Go to previous messageGo to next message
flyboy
Messages: 1886
Registered: November 2006
Senior Member
Thank you for your feedback.

Yes, this requirement makes more sense. Although the initial post was a little misleading as the query posted there returned more than one row if they were "missing".

In any case I would "generate" the MAX+1 value in advance and use it if there are no values smaller. In case you want to return only one row with the smallest value:
with missing_nrs as ( SELECT (min_sno - 1 + level) as missing_number
                      FROM ( SELECT MIN(sno) min_sno, MAX(sno) max_sno
                             FROM sequence )
                      CONNECT BY level <= max_sno - min_sno + 2
                      minus
                      SELECT sno
                      FROM sequence )
select missing_number
from  ( select missing_number from missing_nrs order by missing_number )
where rownum=1
;
Re: Missing number in series of numbers [message #680602 is a reply to message #651463] Sun, 24 May 2020 12:06 Go to previous messageGo to next message
jcgrey87
Messages: 1
Registered: May 2020
Junior Member
oh thanks for the info I had problems like that as well
Re: Missing number in series of numbers [message #680608 is a reply to message #651502] Mon, 25 May 2020 07:19 Go to previous message
Solomon Yakobson
Messages: 2986
Registered: January 2010
Location: Connecticut, USA
Senior Member
Vijay55 wrote on Thu, 19 May 2016 05:24
my requirement is, i want to find out first missing number from a series of numbers, also if there is no missing number in the series then i want to get maximum number +1 of the series.
First of all, sequence is Oracle keyword, so I wouldn't name table SEQUENCE. Now, 11G is de-supported. Upgrade to 12C or higher. Then you can use match_recognize:

select  missing_number
  from  seq
  match_recognize(
                  order by sno
                  measures
                    sno + 1 missing_number
                  pattern (gap)
                  define gap as sno != nvl(next(sno),sno) - 1 and match_number() = 1
                 )
/
For example:

SQL> with seq as (
  2               select 1 sno from dual union all
  3               select 2 from dual union all
  4               select 4 from dual union all
  5               select 5 from dual union all
  6               select 7 from dual union all
  7               select 10 from dual
  8              )
  9  select  missing_number
 10    from  seq
 11    match_recognize(
 12                    order by sno
 13                    measures
 14                      sno + 1 missing_number
 15                    pattern (gap)
 16                    define gap as sno != nvl(next(sno),sno) - 1 and match_number() = 1
 17                   )
 18  /

MISSING_NUMBER
--------------
             3

SQL> with seq as (
  2               select 1 sno from dual union all
  3               select 2 from dual union all
  4               select 3 from dual union all
  5               select 4 from dual union all
  6               select 5 from dual union all
  7               select 6 from dual union all
  8               select 7 from dual union all
  9               select 8 from dual union all
 10               select 9 from dual union all
 11               select 10 from dual
 12              )
 13  select  missing_number
 14    from  seq
 15    match_recognize(
 16                    order by sno
 17                    measures
 18                      sno + 1 missing_number
 19                    pattern (gap)
 20                    define gap as sno != nvl(next(sno),sno) - 1 and match_number() = 1
 21                   )
 22  /

MISSING_NUMBER
--------------
            11

SQL>
And for 11G use:

select  max(sno) + 1 missing_number
  from  seq
  start with sno = (select min(sno) from seq)
  connect by sno = prior sno + 1
/
For example:

SQL> with seq as (
  2               select 1 sno from dual union all
  3               select 2 from dual union all
  4               select 4 from dual union all
  5               select 5 from dual union all
  6               select 7 from dual union all
  7               select 10 from dual
  8              )
  9  select  max(sno) + 1 missing_number
 10    from  seq
 11    start with sno = (select min(sno) from seq)
 12    connect by sno = prior sno + 1
 13  /

MISSING_NUMBER
--------------
             3

SQL> with seq as (
  2               select 1 sno from dual union all
  3               select 2 from dual union all
  4               select 3 from dual union all
  5               select 4 from dual union all
  6               select 5 from dual union all
  7               select 6 from dual union all
  8               select 7 from dual union all
  9               select 8 from dual union all
 10               select 9 from dual union all
 11               select 10 from dual
 12              )
 13  select  max(sno) + 1 missing_number
 14    from  seq
 15    start with sno = (select min(sno) from seq)
 16    connect by sno = prior sno + 1
 17  /

MISSING_NUMBER
--------------
            11

SQL>
SY.

[Updated on: Mon, 25 May 2020 07:25]

Report message to a moderator

Previous Topic: key with different values
Next Topic: Return names of bind variables
Goto Forum:
  


Current Time: Sat Sep 26 01:54:11 CDT 2020