OraFAQ Forum: SQL & PL/SQL » Missing number in series of numbers

Home » SQL & PL/SQL » SQL & PL/SQL » Missing number in series of numbers (Oracle 11g, windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Missing number in series of numbers [message #651463]

Wed, 18 May 2016 06:58

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

Report message to a moderator

Re: Missing number in series of numbers [message #651464 is a reply to message #651463]

Wed, 18 May 2016 07:25

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Missing number in series of numbers [message #651465 is a reply to message #651463]

Wed, 18 May 2016 07:40

flyboy
Messages: 1901
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.

Report message to a moderator

Re: Missing number in series of numbers [message #651502 is a reply to message #651465]

Thu, 19 May 2016 04:24

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:)

Report message to a moderator

Re: Missing number in series of numbers [message #651504 is a reply to message #651502]

Thu, 19 May 2016 05:04

flyboy
Messages: 1901
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
;

Report message to a moderator

Re: Missing number in series of numbers [message #680602 is a reply to message #651463]

Sun, 24 May 2020 12:06

jcgrey87
Messages: 1
Registered: May 2020

Junior Member

oh thanks for the info I had problems like that as well

Report message to a moderator

Re: Missing number in series of numbers [message #680608 is a reply to message #651502]

Mon, 25 May 2020 07:19

Solomon Yakobson
Messages: 3214
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 31 09:58:09 CDT 2023