SQL select items grouped by multiple values-CodePudding

I have table similar this one

instance	value	type
ins_1	31	"A"
ins_1	81	"B"
ins_2	72	"A"
ins_3	9	"B"
ins_3	9	"C"

... and I need select only instance(s) which has double type (A,B). The expected result will be: ["ins1"].

CodePudding user response：

You can do this in multiple ways but I think the fastest should be using exist clause -

SELECT instance
  FROM YOUR_TABLE T1
 WHERE type = '"A"'
   AND EXISTS (SELECT NULL
                 FROM YOUR_TABLE T2
                WHERE T1.instance = T2.instance
                  AND T2.type = '"B"')

CodePudding user response：

The typical approach here is to aggregate the rows per instance and use conditional aggregation in the HAVING clause to get only those instances that match your criteria:

select instance
from mytable
group by instance
having count(*) filter (where type = 'A') > 0
   and count(*) filter (where type = 'B') > 0
order by instance;