Search code examples
sqlpostgresqlselectgaps-and-islandsarray-agg

PostgreSQL array_agg but with stop condition

I have table with record of children & i want to get comma separated results in descending order by month but with a breaking condition of status of child in each month. if status is 0 push it to array but if status is 1 then don't push it and break it there and don't check previous months record.

Table

Children Table

Desired Output:

Desired Output

I have tried it this way which gives me all the months. but i don't know how to break it on status = 1 condition for every child

SELECT name, ARRAY_AGG(month ORDER BY month DESC)
FROM children
GROUP BY name
Solution

  • I'd use a not exists condition to filter out the records you don't want:

    SELECT   name, ARRAY_AGG(month ORDER BY month DESC)
FROM     children a
WHERE    NOT EXISTS (SELECT *
                     FROM   children b
                     WHERE  a.name = b.name AND b.status = 1 and a.month <= b.month)
GROUP BY name