Skip to main content
1 of 5
Andriy M
  • 23.2k
  • 6
  • 59
  • 103

This is a problem. Assuming there are no gaps or duplicates in the same id_set set:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
)
SELECT
  id_set,
  number
FROM counted
WHERE cnt >= 3
;

Here's a SQL Fiddle demo link: http://sqlfiddle.com/#!12/59db6/1.

Andriy M
  • 23.2k
  • 6
  • 59
  • 103