Mysql to select records where values ​​are in sequence

Question

How to select records where ID1 values ​​are in a 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 script

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

Answer 1

If you ask the question: "Give me all the lines that have the next field id1 for the city," and 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 another column, add a condition to the join, for example:

 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 

Answer 2

You can use the EXISTS clause to check the next value in a sequence. This code will only correspond to "sequences" of length> = 2, which, as you see it, 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