Nov 192014
 

In order to find missing numbers from a series in oracle follow following steps:

Step 1: Create one “TEST” table with single column “A”.

Create table test (A number);

Step 2: Insert few random numbers into “TEST” table.

insert into test values (3);

insert into test values (7);

insert into test values (8);

insert into test values (11);

insert into test values (20);

commit;

Step 3: Select the ‘TEST’ table to fetch entered numbers in step 2.

Select * from test;

 

A

3

7

8

11

20

 

 

Step 4: Now write a query to find missing numbers up to 20, as “20” is the maximum number in the above entered table.

select level “MISSING NUMBERS” from dual

    connect by level< 20          

      minus

     select A

       from test;

OUTPUT:

MISSING NUMBERS

1

2

4

5

6

9

10

12

13

14

15

16

17

18

19

 

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)