3 Added another version with joins instead of where clauses edited Mar 19 '13 at 6:59 Ununoctium 111 bronze badge ``````select r1.number from some_table r1, some_table r2, some_table r3, some_table r4 where r3.number <= r2.number and r3.number >= r1.number and r3.status = 'FREE' and r2.number = r1.number + 4 and r4.number <= r2.number and r4.number >= r1.number and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = 5 and count(r4.number) = 0 order by r1.number asc limit 1 ; `````` In this case 5 consecutive numbers - therefore difference must be 4 or in other words `count(r3.number) = n` and `r2.number = r1.number + n - 1`. With joins: ``````select r1.number from some_table r1 join some_table r2 on (r2.number = r1.number + :n -1) join some_table r3 on (r3.number <= r2.number and r3.number >= r1.number) join some_table r4 on (r4.number <= r2.number and r4.number >= r1.number) where r3.status = 'FREE' and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = :n and count(r4.number) = 0 order by r1.number asc limit 1 ; `````` ``````select r1.number from some_table r1, some_table r2, some_table r3, some_table r4 where r3.number <= r2.number and r3.number >= r1.number and r3.status = 'FREE' and r2.number = r1.number + 4 and r4.number <= r2.number and r4.number >= r1.number and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = 5 and count(r4.number) = 0 order by r1.number asc limit 1 ; `````` In this case 5 consecutive numbers - therefore difference must be 4 or in other words `count(r3.number) = n` and `r2.number = r1.number + n - 1`. ``````select r1.number from some_table r1, some_table r2, some_table r3, some_table r4 where r3.number <= r2.number and r3.number >= r1.number and r3.status = 'FREE' and r2.number = r1.number + 4 and r4.number <= r2.number and r4.number >= r1.number and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = 5 and count(r4.number) = 0 order by r1.number asc limit 1 ; `````` In this case 5 consecutive numbers - therefore difference must be 4 or in other words `count(r3.number) = n` and `r2.number = r1.number + n - 1`. With joins: ``````select r1.number from some_table r1 join some_table r2 on (r2.number = r1.number + :n -1) join some_table r3 on (r3.number <= r2.number and r3.number >= r1.number) join some_table r4 on (r4.number <= r2.number and r4.number >= r1.number) where r3.status = 'FREE' and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = :n and count(r4.number) = 0 order by r1.number asc limit 1 ; `````` 2 added 8 characters in body edited Mar 18 '13 at 17:12 JNK 16k55 gold badges5151 silver badges9292 bronze badges ```select r1.number from some_table r1, some_table r2, some_table r3, some_table r4 where r3.number <= r2.number and r3.number >= r1.number and r3.status = 'FREE' and r2.number = r1.number + 4 and r4.number <= r2.number and r4.number >= r1.number and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = 5 and count(r4.number) = 0 order by r1.number asc limit 1 ;`````````select r1.number from some_table r1, some_table r2, some_table r3, some_table r4 where r3.number <= r2.number and r3.number >= r1.number and r3.status = 'FREE' and r2.number = r1.number + 4 and r4.number <= r2.number and r4.number >= r1.number and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = 5 and count(r4.number) = 0 order by r1.number asc limit 1 ; `````` In this case 5 consecutive numbers - therefore difference must be 4 or in other words `count(r3.number) = n` and `r2.number = r1.number + n - 1`. ```select r1.number from some_table r1, some_table r2, some_table r3, some_table r4 where r3.number <= r2.number and r3.number >= r1.number and r3.status = 'FREE' and r2.number = r1.number + 4 and r4.number <= r2.number and r4.number >= r1.number and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = 5 and count(r4.number) = 0 order by r1.number asc limit 1 ;``` In this case 5 consecutive numbers - therefore difference must be 4 or in other words `count(r3.number) = n` and `r2.number = r1.number + n - 1`. ``````select r1.number from some_table r1, some_table r2, some_table r3, some_table r4 where r3.number <= r2.number and r3.number >= r1.number and r3.status = 'FREE' and r2.number = r1.number + 4 and r4.number <= r2.number and r4.number >= r1.number and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = 5 and count(r4.number) = 0 order by r1.number asc limit 1 ; `````` In this case 5 consecutive numbers - therefore difference must be 4 or in other words `count(r3.number) = n` and `r2.number = r1.number + n - 1`. 1 answered Mar 18 '13 at 17:09 Ununoctium 111 bronze badge ```select r1.number from some_table r1, some_table r2, some_table r3, some_table r4 where r3.number <= r2.number and r3.number >= r1.number and r3.status = 'FREE' and r2.number = r1.number + 4 and r4.number <= r2.number and r4.number >= r1.number and r4.status = 'ASSIGNED' group by r1.number, r2.number having count(r3.number) = 5 and count(r4.number) = 0 order by r1.number asc limit 1 ;``` In this case 5 consecutive numbers - therefore difference must be 4 or in other words `count(r3.number) = n` and `r2.number = r1.number + n - 1`.