CODEWITHSUNDEEP
sqlpostgresql
dion90
dion90

Reputation: 13

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

Upvotes: 1

Views: 1423

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95043

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.

Upvotes: 4

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

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"')

Upvotes: 0

Related Questions