SQL select items grouped by multiple values

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”].

Answer

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;

What I hadn’t thought of myself is what Isolated suggests in the request comments: Use INTERSECT, which leads to this very simple query:

select instance from mytable where type = 'A'
intersect
select instance from mytable where type = 'B'
order by instance;

I like both approaches just the same here. My first approach is more versatile, though, as you can easily have various conditions in that query without changing it much. For example if you wanted to limit this to those instances that have types A and B and no other type. You’d just add a condition that the type count must be two or that the count for types other than A and B must be zero.