DB2 Build special select

Question

I have a table with multipe primary keys ID and ANUMBER.

ID (PK)	ANUMBER (PK)	NAME	OPTION
1	1	A	true
1	2	B	true
1	3	C	false
2	1	AA	true

So what I want to get by the Select is

ID (PK)	ANUMBER (PK)	NAME	OPTION
1	1	A	true

OR

ID (PK)	ANUMBER (PK)	NAME	OPTION
1	2	B	true

I not know if it is possible to build such a Select.

Precondition is that the ID should be equal and Option is true and it does not matter which row is returned, one row is sufficient, thats why I listed above two results which are valid.

Answer 1

You can do it with the group by and having clauses, and since it doesn't matter which row is returned, you can use min() or max():

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

If the OPTION is boolean then try this :

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

If you don't want to list all of the columns, you can use the window functions row number() and the cumulative sum():

with cte as (
  select *, row_number() over(partition by id order by ANUMBER desc) rn, 
            sum(case when OPTION then 1 else 0 end) over(partition by id order by ANUMBER ROWS UNBOUNDED PRECEDING) as count_
  from mytable
  where OPTION
)
select *
from cte c
where rn = 1 and COUNT_ > 1

Demo here