clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1048191 fiddles created (11053 in the last week).

CREATE TABLE invoices( eventname varchar, quantity varchar, section varchar, rownumber varchar, secondrow varchar, lowseat int, highseat int, status varchar, created_at timestamp default now() not null, updated_at timestamp ); INSERT INTO invoices VALUES ('2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame)' , '2', '227', '15', null, '9', '10', 'DEPLETED', '2019-02-06 00:46:13.286828', null) , ('2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame)' , '2', '227', '15', null, '7', '8', 'DEPLETED', '2019-02-06 00:46:13.286828', null) , ('2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame)' , '2', '227', '14', null, '23', '24', 'DEPLETED', '2019-02-06 00:46:13.286828', null) , ('2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame)' , '1', '227', '13', null, '21', '21', 'DEPLETED', '2019-02-06 00:46:13.286828', null) , ('2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame)' , '8', '227', '14', null, '15', '22', 'DEPLETED', '2019-02-06 00:46:13.286828', null) , ('2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame)' , '1', '227', '14', null, '1', '1', 'DEPLETED', '2019-02-06 00:46:13.286828', null) , ('2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame)' , '2', 'A57', 'GA', null, '1', '2', 'DEPLETED', '2019-02-06 00:46:13.286828', null) , ('2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame)' , '3', 'A57', 'GA', null, '3', '5', 'DEPLETED', '2019-02-06 00:46:13.286828', null) , ('2018 ACC Basketball Tournament - Session 5 (Virginia vs. Clemson and Duke vs. North Carolina)', '3', '228', '14', null, '1', '3', 'DEPLETED', '2019-02-06 00:46:13.286828', null) , ('Penn State Nittany Lions at Pittsburgh Panthers' , '2', '227', 'K' , null, '25', '26', 'DEPLETED', '2019-02-06 00:46:13.286828', null) ;
10 rows affected
 hidden batch(es)


SELECT DISTINCT ON (island) * FROM ( SELECT * , highseat - lowseat AS len -- off by 1, but irrelevant , count(gap) OVER (ORDER BY rn) AS island FROM ( SELECT * , (lowseat > max(highseat) OVER w) OR NULL AS gap , row_number() OVER w AS rn FROM invoices WINDOW w AS (ORDER BY lowseat, highseat DESC -- longest range 1st ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) ) sub1 ) sub2 ORDER BY island, len, lowseat; -- break ties by picking smallest numbers
eventname quantity section rownumber secondrow lowseat highseat status created_at updated_at gap rn len island
2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame) 1 227 14 1 1 DEPLETED 2019-02-06 00:46:13.286828 3 0 0
2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame) 2 227 15 7 8 DEPLETED 2019-02-06 00:46:13.286828 t 5 1 1
2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame) 2 227 15 9 10 DEPLETED 2019-02-06 00:46:13.286828 t 6 1 2
2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame) 1 227 13 21 21 DEPLETED 2019-02-06 00:46:13.286828 8 0 3
2018 ACC Basketball Tournament - Session 4 (Miami vs North Carolina and Duke vs Notre Dame) 2 227 14 23 24 DEPLETED 2019-02-06 00:46:13.286828 t 9 1 4
Penn State Nittany Lions at Pittsburgh Panthers 2 227 K 25 26 DEPLETED 2019-02-06 00:46:13.286828 t 10 1 5
 hidden batch(es)