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.
Advertisement
Answer
You can use aggregation:
select field1 from t group by field1 having min(field2) <> max(field2);