mysql to select records where values are in sequence

Question

How can I select records where ID1 values are in sequence (of two or more) where TOWN matches

My table

TOWN    ID1
town1   1
town1   2
town1   4 
town2   1
town2   5 
town2   8 
town3   1 
town3   2 
town3   3 

required result

TOWN    ID1
town1   1
town1   2
town3   1 
town3   2 
town3   3 

sql fiddle

http://sqlfiddle.com/#!2/b409f/26

Answer 1

You can use an EXISTS clause to check for the next value in the sequence. This code will only match "sequences" of length >= 2, which seems to be what you want from your example.

SELECT * 
  FROM Table1 a 
 WHERE EXISTS (SELECT * 
                 FROM Table1 b 
                WHERE b.TOWN=a.TOWN 
                  AND b.ID1 IN (a.ID1 - 1, a.ID1 + 1))
 ORDER BY TOWN, ID1

Answer 2

If you question is "give me all rows that have an adjacent id1 field for the town", then simply:

select distinct t1.*
from Table1 t1
join Table1 t2 on t2.town = t1.town and ABS(t1.ID1 - t2.ID1) = 1
order by 1, 2

See SQLFiddle for this.

---

To also match on another column, add the condition to the join, eg:

select distinct t1.*
from Table1 t1
join Table1 t2
  on t2.town = t1.town
  and t2.state = t1.state
  and ABS(t1.ID1 - t2.ID1) = 1
order by 1, 2