Return to Answer

A simple and fast variant:

SELECT min(number) AS first_number, count(*) AS ct_free
FROM (
    SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
    FROM   tbl
    WHERE  status = 'FREE'
    ) x
GROUP  BY grp
HAVING count(*) >= 3  -- minimum length of sequence only goes here
ORDER  BY grp
LIMIT  1;

• Requires a gapless sequence of numbers in number (as provided in the question).

• Works for any number of possible values in status besides 'FREE', even with NULL.

• The major feature is to subtract row_number() from number after eliminating non-qualifying rows. Consecutive numbers end up in the same grp - and grp is also guaranteed to be in ascending order.

• Then you can GROUP BY grp and count the members. Since you seem to want the first occurrence, ORDER BY grp LIMIT 1 and you get starting position and length of the sequence (can be >= n).

###Set of rows To get an actual set of numbers, don't look up the table another time. Much cheaper with generate_series():

SELECT generate_series(first_number, first_number + ct_free - 1)
    -- generate_series(first_number, first_number + 3 - 1) -- only 3
FROM  (
   SELECT min(number) AS first_number, count(*) AS ct_free
   FROM  (
      SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
      FROM   tbl
      WHERE  status = 'FREE'
      ) x
   GROUP  BY grp
   HAVING count(*) >= 3
   ORDER  BY grp
   LIMIT  1
   ) y;

If you actually want a string with leading zeros like you display in your example values, use to_char() with the FM (fill mode) modifier:

SELECT to_char(generate_series(8, 11), 'FM000000')

SQL Fiddle with extended test case and both queries.

Closely related answer:

• Select longest continuous sequence

A simple and fast variant:

SELECT min(number) AS first_number, count(*) AS ct_free
FROM (
    SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
    FROM   tbl
    WHERE  status = 'FREE'
    ) x
GROUP  BY grp
HAVING count(*) >= 3  -- minimum length of sequence only goes here
ORDER  BY grp
LIMIT  1;

• Requires a gapless sequence of numbers in number (as provided in the question).

• Works for any number of possible values in status besides 'FREE', even with NULL.

• The major feature is to subtract row_number() from number after eliminating non-qualifying rows. Consecutive numbers end up in the same grp - and grp is also guaranteed to be in ascending order.

• Then you can GROUP BY grp and count the members. Since you seem to want the first occurrence, ORDER BY grp LIMIT 1 and you get starting position and length of the sequence (can be >= n).

Set of rows

To get an actual set of numbers, don't look up the table another time. Much cheaper with generate_series():

SELECT generate_series(first_number, first_number + ct_free - 1)
    -- generate_series(first_number, first_number + 3 - 1) -- only 3
FROM  (
   SELECT min(number) AS first_number, count(*) AS ct_free
   FROM  (
      SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
      FROM   tbl
      WHERE  status = 'FREE'
      ) x
   GROUP  BY grp
   HAVING count(*) >= 3
   ORDER  BY grp
   LIMIT  1
   ) y;

If you actually want a string with leading zeros like you display in your example values, use to_char() with the FM (fill mode) modifier:

SELECT to_char(generate_series(8, 11), 'FM000000')

SQL Fiddle with extended test case and both queries.

Closely related answer:

• Select longest continuous sequence

A simple and fast variant:

SELECT min(number) AS first_number, count(*) AS ct_free
FROM (
    SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
    FROM   tbl
    WHERE  status = 'FREE'
    ) x
GROUP  BY grp
HAVING count(*) >= 3  -- minimum length of sequence only goes here
ORDER  BY grp
LIMIT  1;

• Requires a gapless sequence of numbers in number (as provided in the question).

• Works for any number of possible values in status besides 'FREE', even with NULL.

• The major feature is to subtract row_number() from number after eliminating non-qualifying rows. Consecutive numbers end up in the same grp - and grp is also guaranteed to be in ascending order.

• Then you can GROUP BY grp and count the members. Since you seem to want the first occurrence, ORDER BY grp LIMIT 1 and you get starting position and length of the sequence (can be >= n).

###Set of rows To get an actual set of numbers, don't look up the table another time. Much cheaper with generate_series():

SELECT generate_series(first_number, first_number + ct_free - 1)
    -- generate_series(first_number, first_number + 3 - 1) -- only 3
FROM  (
   SELECT min(number) AS first_number, count(*) AS ct_free
   FROM  (
      SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
      FROM   tbl
      WHERE  status = 'FREE'
      ) x
   GROUP  BY grp
   HAVING count(*) >= 3
   ORDER  BY grp
   LIMIT  1
   ) y;

If you actually want a string with leading zeros like you display in your example values, use to_char() with the FM (fill mode) modifier:

SELECT to_char(generate_series(8, 11), 'FM000000')

SQL Fiddle with extended test case and both queries.

Closely related answer:

• Select longest continuous sequence

A simple and fast variant:

SELECT min(number) AS first_number, count(*) AS ct_free
FROM (
    SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
    FROM   tbl
    WHERE  status = 'FREE'
    ) x
GROUP  BY grp
HAVING count(*) >= 3  -- minimum length of sequence only goes here
ORDER  BY grp
LIMIT  1;

• Requires a gapless sequence of numbers in number (as provided in the question).

• Works for any number of possible values in status besides 'FREE', even with NULL.

• The major feature is to subtract row_number() from number after eliminating non-qualifying rows. Consecutive numbers end up in the same grp - and grp is also guaranteed to be in ascending order.

• Then you can GROUP BY grp and count the members. Since you seem to want the first occurrence, ORDER BY grp LIMIT 1 and you get starting position and length of the sequence (can be >= n).

###Set of rows To get an actual set of numbers, don't look up the table another time. Much cheaper with generate_series():

SELECT generate_series(first_number, first_number + ct_free - 1)
    -- generate_series(first_number, first_number + 3 - 1) -- only 3
FROM  (
   SELECT min(number) AS first_number, count(*) AS ct_free
   FROM  (
      SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
      FROM   tbl
      WHERE  status = 'FREE'
      ) x
   GROUP  BY grp
   HAVING count(*) >= 3
   ORDER  BY grp
   LIMIT  1
   ) y;

If you actually want a string with leading zeros like you display in your example values, use to_char() with the FM (fill mode) modifier:

SELECT to_char(generate_series(8, 11), 'FM000000')

SQL Fiddle with extended test case and both queries.

Closely related answer:

• Select longest continuous sequence

A simple and fast variant:

SELECT min(number) AS first_number, count(*) AS ct_free
FROM (
    SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
    FROM   tbl
    WHERE  status = 'FREE'
    ) x
GROUP  BY grp
HAVING count(*) >= 3  -- minimum length of sequence only goes here
ORDER  BY grp
LIMIT  1;

• Requires a gapless sequence of numbers in number (as provided in the question).

• Works for any number of possible values in status besides 'FREE', even with NULL.

• The major feature is to subtract row_number() from number after eliminating non-qualifying rows. Consecutive numbers end up in the same grp - and grp is also guaranteed to be in ascending order.

• Then you can GROUP BY grp and count the members. Since you seem to want the first occurrence, ORDER BY grp LIMIT 1 and you get starting position and length of the sequence (can be >= n).

###Set of rows To get an actual set of numbers, don't look up the table another time. Much cheaper with generate_series():

SELECT generate_series(first_number, first_number + ct_free - 1)
    -- generate_series(first_number, first_number + 3 - 1) -- only 3
FROM  (
   SELECT min(number) AS first_number, count(*) AS ct_free
   FROM  (
      SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
      FROM   tbl
      WHERE  status = 'FREE'
      ) x
   GROUP  BY grp
   HAVING count(*) >= 3
   ORDER  BY grp
   LIMIT  1
   ) y;

If you actually want a string with leading zeros like you display in your example values, use to_char() with the FM (fill mode) modifier:

SELECT to_char(generate_series(8, 11), 'FM000000')

SQL Fiddle with extended test case and both queries.

Closely related answer:

• Select longest continuous sequence

A simple and fast variant:

SELECT min(number) AS first_number, count(*) AS ct_free
FROM (
    SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
    FROM   tbl
    WHERE  status = 'FREE'
    ) x
GROUP  BY grp
HAVING count(*) >= 3  -- minimum length of sequence only goes here
ORDER  BY grp
LIMIT  1;

• Requires a gapless sequence of numbers in number (as provided in the question).

• Works for any number of possible values in status besides 'FREE', even with NULL.

• The major feature is to subtract row_number() from number after eliminating non-qualifying rows. Consecutive numbers end up in the same grp - and grp is also guaranteed to be in ascending order.

• Then you can GROUP BY grp and count the members. Since you seem to want the first occurrence, ORDER BY grp LIMIT 1 and you get starting position and length of the sequence (can be >= n).

###Set of rows To get an actual set of numbers, don't look up the table another time. Much cheaper with generate_series():

SELECT generate_series(first_number, first_number + ct_free - 1)
    -- generate_series(first_number, first_number + 3 - 1) -- only 3
FROM  (
   SELECT min(number) AS first_number, count(*) AS ct_free
   FROM  (
      SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
      FROM   tbl
      WHERE  status = 'FREE'
      ) x
   GROUP  BY grp
   HAVING count(*) >= 3
   ORDER  BY grp
   LIMIT  1
   ) y;

If you actually want a string with leading zeros like you display in your example values, use to_char() with the FM (fill mode) modifier:

SELECT to_char(generate_series(8, 11), 'FM000000')

SQL Fiddle with extended test case and both queries.

Closely related answer:

• Select longest continuous sequence