Table name is SEATS_TABLE and following are the rows of the table:
SEAT_NO
|
BOOKED
|
A1
|
N
|
A2
|
N
|
A3
|
Y
|
A4
|
N
|
A5
|
N
|
A6
|
Y
|
A7
|
N
|
A8
|
N
|
A9
|
N
|
A10
|
Y
|
A11
|
N
|
A12
|
N
|
A13
|
Y
|
A14
|
N
|
B1
|
N
|
B2
|
N
|
B3
|
N
|
B4
|
Y
|
B5
|
N
|
B6
|
Y
|
B7
|
N
|
B8
|
N
|
B9
|
N
|
B10
|
Y
|
B11
|
Y
|
B12
|
N
|
B13
|
N
|
B14
|
N
|
Solution 1 :
with not_booked_seats as (select seat_no,
lead(seat_no, 1, 0) over(order by substr(seat_no, 1, 1), to_number(substr(seat_no, 2))) seat_no_2,
lead(seat_no, 2, 0) over(order by substr(seat_no, 1, 1), to_number(substr(seat_no, 2))) seat_no_3
from Seats_table t1
where t1.booked = 'N')
select * from not_booked_seats
where to_number(substr(seat_no,2))+1= to_number(substr(seat_no_2,2))
and to_number(substr(seat_no,2))+2= to_number(substr(seat_no_3,2))
Solution 2:
select *
from (select t1.seat_no col1, t2.seat_no col2, t3.seat_no col3
from Seats_table t1, Seats_table t2, Seats_table t3
where t1.booked = 'N'
and t2.booked = 'N'
and t3.booked = 'N')
where substr(col1, 2) + 1 = substr(col2, 2)
and substr(col2, 2) + 1 = substr(col3, 2)
and substr(col1,1,1) = substr(col2,1,1)
and substr(col2,1,1) = substr(col3,1,1)
Related Question :
Find three consecutive available numbers from a table.
add to del.icio.us
saved by 0 users
