Check existence of a value in a partition using window function in oracle sql

Question

I am having below set of data.

My goal is to have last column Exists with value YES when value C101808491229 exists in the partition (PARTITION BY COLUMN2), else with value NO

I was tried to do so in below two ways :

1.COUNT(CASE WHEN COLUMN5 = 'C101808491229' THEN 'YES' ELSE 'NO' END) OVER (PARTITION BY COLUMN2) AS EXISTS

2.CASE WHEN COLUMN5 = 'C101808491229' THEN 'YES' ELSE 'NO' END AS EXISTS

But neither seemed to work for me.

I hope I made my point clear. Can someone let me know if it is possible to achieve this?

Answer 1

You can simply use the MAX as follows:

MAX(CASE WHEN COLUMN5 = 'C101808491229' THEN 'YES' ELSE 'NO' END) 
 OVER (PARTITION BY COLUMN2) AS EXISTS

If C101808491229 exists then it will return YES and max will always give YES if there is even one YES and other NO exists in the partition.