How can I query for the distinct field1 instances that have multiple distinct corresponding field2 values? field1 field2 a apple b grape c banana b orange a apple In this example I want to ...

Select unique field1 that has >1 distinct instances of field2 associated with it?

How can I query for the distinct field1 instances that have multiple distinct corresponding field2 values?

field1	field2
a	apple
b	grape
c	banana
b	orange
a	apple

In this example I want to return “b”, since there are at least 2 distinct values (grape and orange) for field2 that correspond to it. I don’t wan’t “a” since there is only 1 unique field2 value that corresponds, “apple”.

I have tried

with all_unique_combos as (
select distinct field1, field2
from table
)

select field1
from all_unique_combos
group by field1
having count(field2) > 1

I actually think this is right and would give me what I need. But at the moment it’s returning 0 rows so I kinda need a sanity check. Thanks for any input either way.

Answer

You can use aggregation:

select field1
from t
group by field1
having min(field2) <> max(field2);

Advertisement

Answer