Finding only missing sequence number with id

Question

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.

I have two Tables

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

Below Query works in oracle but I want query which executes in SQL serevr2008.

SELECT r id,rn seq FROM(SELECT ROWNUM rn FROM all_objects WHER 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)

Can you edit your question and show what you want as output? — Gordon Linoff
– Gordon Linoff, Commented Nov 1, 2014 at 12:39

Gordon Linoff · Accepted Answer · 2014-11-01 12:44:27Z

2

You can do this by generating all the possible numbers and then finding the ones that don't match. The following should work in both Oracle and SQL Server:

with nums(id, seqnum, mx)  as (
      select t.id, t.mn as seqnum, t.mx
      from test_number_min_max t
      union all
      select t.id, t.mn + 1, t.mx
      from nums
      where nums.seqnum < t.mx
     )
select nums.id, nums.seqnum
from nums left outer join
     test_number tn
     on tn.id = nums.id and tn.seqnumber = nums.seqnumber
where tn.id is null;

If there are more than 100 values between the minimum and maximum, then you will need to set the maximum recursion. If performance is an issue, you might want another way of generating the numbers. Here is one possibility (that should also work in both databases):

with nums as (
      select row_number() over (order by id) - 1 as n
      from test_number
     )
select tnmm.id, tnmm.mn + nums.n as seqnumber
from test_number_min_max tnmm join
     nums
     on tnmm.mn + nums.n <= tnmm.mx left join
     test_number tn
     on tn.id = tnmm.id and
        tn.seqnumber = tnmm.mn + nums.n
where tn.id is null;

This assumes that there are enough rows in test_number to enumerate the largest range in test_number_min_max (a reasonable assumption, but it might not be true).

answered Nov 1, 2014 at 12:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sandesh Over a year ago

Thanks Gordon Linoff :)

wildplasser · Accepted Answer · 2014-11-01 13:38:26Z

CREATE TABLE test_number(id INTEGER,SEQ INTEGER,text varchar) ;
INSERT INTO test_number VALUES
  (1,1,'AA') 
        , (1,3,'CC') , (1,4,'DD') , (1,5,'EE') , (1,6,'FF') , (1,7,'GG') 
        , (1,8,'HH') , (1,10,'JJ')
, (2,1,'KK') , (2,2,'LL') , (2,3,'MM') , (2,4,'NN') 
       , (2,6,'PP') , (2,7,'QQ')
, (3,1,'TT') 
        , (3,4,'ZZ') , (3,5,'XX')
        ;

SELECT t1.id AS "Id"
        , t1.seq+1 AS "Start"
        , t2.seq-1 AS "Stop"
        , t2.seq - t1.seq -1 AS "Gapsize"
FROM test_number t1
JOIN test_number t2 ON t2.id = t1.id AND t2.seq > t1.seq + 1
WHERE NOT EXISTS (
        SELECT * FROM test_number nx
        WHERE nx.id = t1.id
        AND nx.seq > t1.seq
        AND nx.seq < t2.seq
        );

Result:

 Id | Start | Stop | Gapsize 
----+-------+------+---------
  1 |     2 |    2 |       1
  1 |     9 |    9 |       1
  2 |     5 |    5 |       1
  3 |     2 |    3 |       2
(4 rows)

Collectives™ on Stack Overflow

Finding only missing sequence number with id

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related