Joe Celko's SQL for Smarties, 3rd Edition

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. ...