24.3. Finding Regions of Maximum Size
A query to find a region, rather than a subregion of a known size, of seats was presented in SQL Forum (Rozenshtein, Abramovich, and Birger 1993).
SELECT T1.seat_nbr, ' thru ', T2.seat_nbr FROM Theater AS T1, Theater AS T2 WHERE T1.seat_nbr < T2.seat_nbr AND NOT EXISTS (SELECT * FROM Theater AS T3 WHERE (T3.seat_nbr BETWEEN T1.seat_nbr AND T2.seat_nbr AND T3.occupancy_status <> 'A') OR (T3.seat_nbr = T2.seat_nbr + 1 AND T3.occupancy_status = 'A') OR (T3.seat_nbr = T1.seat_nbr - 1 AND T3.occupancy_status = 'A'));
The trick here is to look for the starting and ending seats in the region. The starting seat_nbr of a region is to the right of a sold seat_nbr, and the ending seat_nbr is to the left of a sold seat_nbr. ...