I have a table in a bucket, I am using Athena to get the required data My table looks like
x
resourceid appname
i-1 A-1
i-1 A-2
i-1 A-3
i-2 A-3
i-2 A-2
I need to find all the resources where A-1 is not found the result should give me i-2. How to write in sql
Advertisement
Answer
You can use aggregation to group all rows having the same resourceid
together, and then filter out groups of rows where appname
'A-1'
does not appear:
select resourceid
from mytable
group by resourceid
having max(case when appname = 'A-1' then 1 else 0 end) = 0