CODEWITHSUNDEEP
sqloracle10g
Bhavana
Bhavana

Reputation: 349

How I find missing elements in a series using SQL?

I have a table bill with column name bill_id. bill_id value range is from 1 to 40. And I have rows like 

bill_id
-----------
1  
3  
6  
8  
2  
21  
34  
35  
26  
40

How can I find the missing elements (4, 5, 7, 9, 10, etc.)?

Upvotes: 0

Views: 211

Answers (1)

Rob van Wijk
Rob van Wijk

Reputation: 17705

SQL> create table bill (bill_id)
  2  as
  3  select 1 from dual union all
  4  select 3 from dual union all
  5  select 6 from dual union all
  6  select 8 from dual union all
  7  select 2 from dual union all
  8  select 21 from dual union all
  9  select 34 from dual union all
 10  select 35 from dual union all
 11  select 26 from dual union all
 12  select 40 from dual
 13  /

Table created.

SQL> with all_possible_bill_ids as
  2  ( select level bill_id
  3      from dual
  4   connect by level <= 40
  5  )
  6  select bill_id
  7    from all_possible_bill_ids
  8   minus
  9  select bill_id
 10    from bill
 11  /

   BILL_ID
----------
         4
         5
         7
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        22
        23
        24
        25
        27
        28
        29
        30
        31
        32
        33
        36
        37
        38
        39

30 rows selected.

Regards,
Rob.

Upvotes: 8

Related Questions