CODEWITHSUNDEEP
sqlgaps-and-islands
Sandesh
Sandesh

Reputation: 119

Finding only missing sequence number with id

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)

Upvotes: 1

Views: 297

Answers (2)

wildplasser
wildplasser

Reputation: 44240

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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

Upvotes: 2

Related Questions