MySQL finding gaps in column with multiple ID

Question

I would like to find gaps in following table:

create table sequence
(
   `Id` int,
   `Value` int not null,
   PRIMARY KEY (`Id`,`Value`)
);

insert into sequence
    ( `Id`, `Value` )
  values
    (10, 0 ),
    (10, 1 ),
    (10, 4 ),
    (10, 5 ),
    (10, 6 ),
    (10, 7 ),
    (11, 0 ),
    (11, 1 ),
    (11, 2 ),
    (11, 5 ),
    (11, 7 );

Expeced result is somthing like:

10 | 2-3
11 | 3-4
11 | 6

or

I know, that value of the colum 'Value' is between 0 and 7.

Is it possible to do it using MySQL?

EDIT 1

Based on answers I come with this:

SELECT Tbl1.Id, 
       startseqno, 
       Min(B.Value) - 1 AS END 
FROM   (SELECT Id, 
               Value + 1 AS StartSeqNo 
        FROM   SEQUENCE AS A 
        WHERE  NOT EXISTS (SELECT * 
                           FROM   SEQUENCE AS B 
                           WHERE  B.Id = A.id 
                                  AND B.Value = A.Value + 1) 
               AND Value < (SELECT Max(Value) 
                            FROM   SEQUENCE B 
                            WHERE  B.Id = A.Id)) AS Tbl1, 
       SEQUENCE AS B 
WHERE  B.Id = Tbl1.Id 
       AND B.Value > Tbl1.startseqno

But now I am getting just

10 | 2 | 3

Please, does somebody know, how to fix it?

sqlfiddle

Yes, that's possible. Read on here: http://stackoverflow.com/questions/9787038/sql-group-by-intervals-in-continuity — Kaii, Apr 30 '16 at 14:54
please see the link http://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql — Afshan Shujat, Apr 30 '16 at 14:56
@kali Thank you, I have read it and come with some code, but still it is not what I need — Krivers, Apr 30 '16 at 19:10
@AfshanShujat Thank you, I have read it and come with some code, but still it is not what I need — Krivers, Apr 30 '16 at 19:10

Gordon Linoff · Accepted Answer · 2016-05-01T01:46:28.953

You can do this with not exists:

select s.*
from sequence s
where not exists (select 1 from sequence s2 where s2.id = s.id and s2.value = s.value + 1) and
      exists (select 1 from sequence s2 where s2.id = s.id and s2.value > s.value);

The exists clause is important so you don't report the final value for each id.

EDIT:

Here is a better approach:

select s.value + 1 as startgap,
       (select min(s2.value) - 1 from sequence s2 where s2.id = s.id and s2.value > s.value) as endgap
from sequence s
where not exists (select 1 from sequence s2 where s2.id = s.id and s2.value = s.value + 1) and
      exists (select 1 from sequence s2 where s2.id = s.id and s2.value > s.value);

Thank you for your answer, but I need also the end of the each gap. Is it possible to extend this command to include it? — Krivers, Apr 30 '16 at 19:12

MySQL finding gaps in column with multiple ID

1 Answers1