Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable (
  ID int,	
  ANUMBER int,
  NAME	varchar(10),
  OPTION varchar(10)
);

insert into mytable values(1,	2,	'A',	'true');
insert into mytable values(1,	2,	'B',	'true');
insert into mytable values(1,	3,	'C',	'false');
insert into mytable values(2,	2,	'AA',	'true');
select *
from mytable


ID
ANUMBER
NAME
OPTION


1
2
A
true

1
2
B
true

1
3
C
false

2
2
AA
true


select ID, MAX(ANUMBER) as ANUMBER, MAX(NAME) as NAME, 'true' AS OPTION
from mytable
where OPTION = 'true'
group by ID
having sum(case when OPTION = 'true' then 1 else 0 end) > 1


ID
ANUMBER
NAME
OPTION


1
2
B
true