Find the missing se...

# Find the missing sequence.

(@demonguru)
Joined: 6 years ago
Posts: 36
25/04/2018 2:25 pm

Find the missing sequence. Table test_number contains the sequence for each id. Table test_number_min_max contains the minimum and maximum number for each id. We need to find the missing number between the minimum and maximum number for each id. text column can be ignored.

TABLE

CREATE TABLE test_number(id NUMBER,SEQ NUMBER,text VARCHAR2(5)) ;

INSERT INTO test_number VALUES(1,1,'AA');

INSERT INTO test_number VALUES(1,3,'CC');

INSERT INTO test_number VALUES(1,4,'DD');

INSERT INTO test_number VALUES(1,5,'EE');

INSERT INTO test_number VALUES(1,6,'FF');

INSERT INTO test_number VALUES(1,7,'GG');

INSERT INTO test_number VALUES(1,8,'HH');

INSERT INTO test_number VALUES(1,10,'JJ');

INSERT INTO test_number VALUES(2,1,'KK');

INSERT INTO test_number VALUES(2,2,'LL');

INSERT INTO test_number VALUES(2,3,'MM');

INSERT INTO test_number VALUES(2,4,'NN');

INSERT INTO test_number VALUES(2,6,'PP');

INSERT INTO test_number VALUES(2,7,'QQ');

INSERT INTO test_number VALUES(3,1,'TT');

INSERT INTO test_number VALUES(3,4,'ZZ');

INSERT INTO test_number VALUES(3,5,'XX');

create tabel test_number_min_max(id number,mn number,mx  number);

INSERT INTO test_number_min_max VALUES(1,1,12);

INSERT INTO test_number_min_max VALUES(2,1,9);

INSERT INTO test_number_min_max VALUES(3,1,5);

SELECT r id,rn seq FROM (SELECT ROWNUM rn FROM all_objects WHERE ROWNUM <13),

(SELECT ROWNUM r FROM all_objects

WHERE ROWNUM <4),test_number_min_max m

WHERE r=id

AND rn >= mn

AND rn <= mx

AND (r,rn) NOT IN

(SELECT id,seq FROM test_number)

SELECT r id ,l seq FROM

(SELECT LEVEL l FROM dual

CONNECT BY LEVEL <13),

(SELECT LEVEL r FROM dual CONNECT BY LEVEL <4),

test_number_min_max

WHERE r=id

AND  l>=mn

AND l<=mx

AND (r,l) NOT IN (SELECT id,seq FROM test_number)

OUTPUT

ID SEQ
1 2
1 9
1 11
1 12
2 5
2 8
2 9
3 2
3 3

DemonGuru18

Share: