tomen tomen - 7 months ago 19
SQL Question

Get all sequential block from a list

i have a list of number in mysql like that

column 1 column2 column 3
4
6
7
88
21
29
30
31


How can i get all sequential blocks, result should be

6
7
29
30
31

Answer

You can use exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.col1 + 1 = t.col1
             ) or
      exists (select 1
              from t t2
              where t2.col1 - 1 = t.col1
             ) ;

You can combine the exists into a single subquery:

select t.*
from t
where exists (select 1
              from t t2
              where t2.col1 in (t.col1 - 1, t.col1 + 1)
             );

The first version should be able to make use of an index on the column. It might be more difficult for an optimize to use an index for the second.

Also note that these versions allow you to include other columns from the rows as well.