sql : get consecutive group 'n' rows (could be inbetween)

Question

Below is my theater table:

create table theater
(
   srno integer, 
   seatno integer, 
   available boolean
);

insert into theater
values
(1, 100,true),
(2, 200,true),
(3, 300,true),
(4, 400,false),
(5, 500,true),
(6, 600,true),
(7, 700,true),
(8, 800,true);

I want a sql which should take input as 'n' and returns me the first 'n' consecutive available seats, like

• if n = 2 output should be 100,200
• if n = 4 output should be 500,600,700,800

NOTE: I am trying to build an query for postgres 9.3

Answer 1

In SQL-Server you can do It in following:

DECLARE @num INT = 4

;WITH cte AS
(
SELECT *,COUNT(1) OVER(PARTITION BY cnt) pt  FROM
(
    SELECT tt.*
        ,(SELECT COUNT(srno) FROM theater t WHERE available <> 'true' and srno < tt.srno) AS cnt
    FROM  theater tt
    WHERE available = 'true'
) t1
)
SELECT TOP (SELECT @num) srno, seatno, available
FROM cte
WHERE pt >= @num

OUTPUT

srno    seatno  available
5       500 true
6       600 true
7       700 true
8       800 true

Answer 2

This will find the available seats. written for sqlserver 2008+:

DECLARE @num INT = 4

;WITH CTE as
(
  SELECT 
    srno-row_number() over (partition by available order by srno) grp, 
    srno, seatno, available
  FROM theater
), CTE2 as
(
  SELECT grp, count(*) over (partition by grp) cnt,
    srno, seatno, available 
  FROM CTE
    WHERE available = 'true'

)
SELECT top(@num)
  srno, seatno, available
FROM CTE2
WHERE cnt >= @num
ORDER BY srno

Result:

srno  seatno  available
5     500     1
6     600     1
7     700     1
8     800     1