3 Added another version with joins instead of where clauses
source | link
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
source | link

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
source | link

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.