Selecting row grouped by an "add-on" indicator

Question

I have a (pre-existing, no I did not design it like this!) schema / dataset like this:

CREATE TABLE t(id,name,seq,addon,kind)
AS VALUES
    ( 1234::int, 'A' , 345::int, 'f'::bool, 'c'  ),
    ( 1235     , NULL, 346     , 't'      , NULL ),
    ( 1236     , NULL, 347     , 't'      , NULL ),
    ( 2345     , 'B' , 348     , 'f'      , 'x'  ),
    ( 2346     , NULL, 349     , 't'      , NULL ),
    ( 3456     , 'C' , 350     , 'f'      , 'c'  ),
    ( 3457     , NULL, 351     , 't'      , NULL );

Where:

1. The "id" field is just a SQL sequence (order not guaranteed)
2. The "seq" field is guaranteed to be sequential throughout the table, e.g. table is normally sorted on this column.
3. The "addon" field is indicating a group of related entries, e.g. TRUE entries are related to the nearest FALSE entry with a lower "seq".

I want to SELECT all the rows with "kind" = 'x' and all of their associated "addon" rows, without getting any other "addon" rows for other kinds.

So for instance:

SELECT * FROM t WHERE kind = 'x' AND ???

Would give me:

  id  | name | seq  |addon | kind |
------+------+------+------+------+...
 2345 | "B"  | 348  |  f   | "x"  |
 2346 |      | 349  |  t   |      |

I have PostgreSQL 9.6...

Answer 1

You just need to create a real grouping. Here we use a Window Function count(x) which counts all things NOT NULL. Try it with this simplified example,

SELECT count(a) OVER ()
FROM ( VALUES (true),(false) ) AS t(a);

In order to count just what we want, we use OR which evaluates the rhs if the lhs is not true. So here we test if the row IS FALSE (returning true if the value is false), if the condition does not evaluate to true (meaning the value is true or null) we return null (so count(x) skips it). here is the full query,

SELECT *,
  count((addon IS FALSE) OR NULL) OVER (ORDER BY seq) AS grp
FROM t

  id  | name | seq | addon | kind | grp 
------+------+-----+-------+------+-----
 1234 | A    | 345 | f     | c    |   1
 1235 |      | 346 | t     |      |   1
 1236 |      | 347 | t     |      |   1
 2345 | B    | 348 | f     | x    |   2
 2346 |      | 349 | t     |      |   2
 3456 | C    | 350 | f     | c    |   3
 3457 |      | 351 | t     |      |   3
(7 rows)

now you can easily do anything you want..

WITH t AS (
  SELECT *,
    count((addon IS FALSE) OR NULL) OVER (ORDER BY seq) AS grp
  FROM t
)
SELECT *
FROM t AS t1
WHERE EXISTS (
  SELECT 1
  FROM t AS t2
  WHERE addon IS FALSE
    AND kind = 'x'
    AND t1.grp = t2.grp
)
ORDER BY seq;

To see this pattern elsewhere, check out the gaps-and-islands and postgresql, using this search

Answer 2

This is a classic example of GROUPING OR WINDOWS

create table test(id int, name varchar(10), seq int, addon bool, kind varchar(10));
insert into test values
( 1234, 'A', 345, false, 'c'),
( 1235, null, 346, true, null),
( 1236, null, 347, true, null),
( 2345, 'B', 348, false, 'x'),
( 2346, null, 349, true, null),
( 3456, 'C', 350, false, 'c'),
( 3457, null, 351, true, null);

with ctrst as
(
    select id, name, seq, addon, kind,
           case when kind is not null then 1 end as rst
    from   test
)
, ctgrp as
(
    select id, name, seq, kind,
           count(rst) over (order by seq) as grp
     from  ctrst
)
select id, name, seq, kind 
from   ctgrp
where  grp = (select grp from ctgrp where kind = 'x')
;

  id | name | seq | kind
---: | :--- | --: | :---
2345 | B    | 348 | x   
2346 | null | 349 | null

dbfiddle here

Answer 3

I'm assuming from your example data that you can tell if a row is an 'addon' by checking if the kind field is null. If that's the case you can use a windowing count on kind to group the rows into 'islands':

select id, name, seq, addon, kind
from ( select *, max(kind) over (partition by grp) grp_kind
       from ( select *, count(kind) over (order by seq) grp from t ) z ) z
where grp_kind='x';

  id | name | seq | addon | kind
---: | :--- | --: | :---- | :---
2345 | B    | 348 | f     | x   
2346 | null | 349 | t     | null

dbfiddle here

If that assumption doesn't hold you can use count(nullif(addon,true)) to the same effect, or sum(1-addon::int):

select id, name, seq, addon, kind
from ( select *, max(kind) over (partition by grp) grp_kind
       from ( select *, sum(1-addon::int) over (order by seq) grp from t ) z ) z
where grp_kind='x';

  id | name | seq | addon | kind
---: | :--- | --: | :---- | :---
2345 | B    | 348 | f     | x   
2346 | null | 349 | t     | null

dbfiddle here