Athena sql query to find items not containing a value

I have a table in a bucket, I am using Athena to get the required data My table looks like

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

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