select all “groups/partitions” where parameter is found

Without going into too much detail – I need to create groups (grouped on a specific field) of data and then display all GROUPS of records that contain a parameter. I need all records in a GROUP even if some do not match the parameter. Any GROUPS where no records contain the parameter would be suppressed.

I’m working with db2 and I just need help with the basic syntax. I’m thinking a PARTITION_BY used within a subquery might be the correct approach. Any ideas? Thanks in advance.

Answer

Does it answer the question ?

with table1 (group_column, expression, other_column) as (
  values
  ('group1', 'false', 'First in G1'),
  ('group1', 'false', 'Second in G1'),
  ('group2', 'false', 'First in G2'),
  ('group2', 'true', 'Second in G2'),
  ('group3', 'true', 'Full G3')
)
select
  table1.group_column, expression, other_column 
from table1
  inner join
  (
    select
      distinct group_column
    from table1
    where expression = 'true'
  ) as groups on table1.group_column = groups.group_column

GROUP_COLUMN	EXPRESSION	OTHER_COLUMN
group2	false	First in G2
group2	true	Second in G2
group3	true	Full G3