I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ASSIGNED 1 000004 FREE 1 000005 FREE 1 000006 ASSIGNED 1 000007 ASSIGNED 1 000008 FREE 1 000009 FREE 1 000010 FREE 1 000011 ASSIGNED 1 000012 ASSIGNED 1 000013 ASSIGNED 1 000014 FREE 1 000015 ASSIGNED
and I need to find "n" consecutive numbers, so for n = 3, query would return
1 000008 FREE 1 000009 FREE 1 000010 FREE
It should return only first possible group of each id_set (in fact, it would be executed only for id_set per query)
I was checking WINDOW functions, tried some queries like
COUNT(id_number) OVER (PARTITION BY id_set ROWS UNBOUNDED PRECEDING), but that's all I got :) I couldn't think of logic, how to do that in Postgres.
I was thinking about creating virtual column using WINDOW functions counting preceding rows for every number where status = 'FREE', then select first number, where count is equal to my "n" number.
Or maybe group numbers by status, but only from one ASSIGNED to another ASSIGNED and select only groups containing at least "n" numbers
I found this query (and changed it a little bit)
WITH q AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id_set, status ORDER BY number) AS rnd, ROW_NUMBER() OVER (PARTITION BY id_set ORDER BY number) AS rn FROM numbers ) SELECT id_set, MIN(number) AS first_number, MAX(number) AS last_number, status, COUNT(number) AS numbers_count FROM q GROUP BY id_set, rnd - rn, status ORDER BY first_number
which produces groups of FREE/ASSIGNED numbers, but I would like to have all numbers from only first group which meets the condition