MYSQL: Find missing values in a sequence by different groups

Question

Hy, little help please, I have a full sequence of 12 items, I identify these items by 2 different groups (12345 and 54321). Now, I need to identify the first sequence of the item "12345" stop at 4 and restart at 10. Something like that:

I have this table:

------------------
|seq  |partNumber|
------------------
| 1   |  12345   |
| 2   |  12345   |
| 3   |  12345   |
| 4   |  12345   |
| 10  |  12345   |
| 11  |  12345   |
| 12  |  12345   |
| 5   |  54321   |
| 6   |  54321   |
| 7   |  54321   |
| 8   |  54321   |
| 9   |  54321   |
------------------

I need to find this result:

------------
|Start|Stop|
------------
|  5  |  9 | (partnumber:12345)
------------

the query that I used:

select start, stop from (
 select m.partNumber + 1 as start,
    (select min(partNumber) - 1 from seq as x where x.partNumber > m.partNumber) as stop
  from seq as m
    left outer join seq as r on m.partNumber = r.partNumber - 1 where r.partNumber is null) as x
where stop is not null;

But, this query gives me this result:

------------
|Start|Stop|
------------
|  9  |  9 | (partnumber:12345)
------------

Final result: I want to identify sequence of "12345" starts in 1 ends in 4 (break) restart at 10 ends 12, I have a gap in 5 to 9. The other sequence of "54321" starts at 5 ends in 9, here I don't have a gap.

Can you, in this application, assign an upper bound to the value of `seq`? — O. Jones, May 24 '16 at 14:32
Possible duplicate of [How to find gaps in sequential numbering in mysql?](http://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql) — nada, May 24 '16 at 14:33
Its the same result. So it doesn't work for me, I already tested. Thanks nada — Gomez, May 24 '16 at 14:43
What is the largest value of `seq` your application will ever encounter? — O. Jones, May 24 '16 at 14:50

score 0 · Accepted Answer · answered May 24 '16 at 15:08

This is a good application for the structured part of structured query language.

I guess you will never have a value of seq less than zero or greater than the arbitary value 15624. That guess is important: we need a table of all the cardinal numbers in that range to get missing-number detection to work.

Here is such a table

SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq
  FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F

(If you're using MariaDB, you can use the Sequence table seq_0_to_15624 in place of this lump of SQL code.)

Next, you need a way to find out the lowest and highest value of seq for each part number. You do that like so.

       SELECT partNumber, MIN(seq) minSeq, MAX(seq) maxSeq
         FROM seq
        GROUP BY partNumber

Next, you need to generate a table showing all the possible sequence numbers from minimum to maximum for each part number:

SELECT cardinals.seq, r.partNumber
  FROM (
        SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq  
          FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
          JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F
       ) cardinals
  JOIN (
         SELECT partNumber, MIN(seq) minSeq, MAX(seq) maxSeq
           FROM seq
          GROUP BY partNumber
   ) r ON cardinals.seq >= r.minSeq AND cardinals.seq <= r.maxSeq

Finally, you can LEFT JOIN that to your original table and do WHERE val IS NULL to locate your missing sequence numbers.

SELECT cardinals.seq, r.partNumber
  FROM (
        SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq  
          FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
          JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F
           ) cardinals
  JOIN (
             SELECT partNumber, MIN(seq) minSeq, MAX(seq) maxSeq
               FROM seq
              GROUP BY partNumber
       ) r ON cardinals.seq >= r.minSeq AND cardinals.seq <= r.maxSeq
  LEFT JOIN seq ON cardinals.seq = seq.seq AND r.PartNumber = seq.partNumber
 WHERE seq.seq IS NULL

Ok, that works. Thanks Ollie, but can we group them like: Start - 5 ; Stop - 9? I'll try — Gomez, May 24 '16 at 15:19
Find it: SELECT min(cardinals.seq) as START, SELECT max(cardinals.seq) as STOP, r.partNumber (...) Once more, Thanks Ollie, save my day. — Gomez, May 24 '16 at 16:04
Hi, Ollie Jones, the query generate an **NULL**: I've sequence 7298 up to 7478 of one part number but in the middle I've other part number(the sequence of this part number is 7375 to 7434) and this query don't find this gap. Supposedly the result would be 7298 - 7374(one part number); 7375 - 7434 (the other part number) and finally 7435 - 7478(the first part number again). can you help me? — Gomez, May 27 '16 at 14:41

MYSQL: Find missing values in a sequence by different groups

1 Answers1