Reputation: 83
Given a dataset that looks like this.
ID_no | Color
----------------
1 | Blue
1 | Blue
2 | Green
2 | Blue
3 | Red
4 | Red
4 | Blue
How do I only return the IDs that have one unique color? i.e
ID_no | Color
----------------
1 | Blue
1 | Blue
3 | Red
I tried
select ID_no, color from table
having count(unique(color)) = 1
GROUP BY ID_no
But that doesn't seem to work, can someone help me out with the SQL syntax to do this?
Upvotes: 0
Views: 222
Reputation: 1221
Hope this helps.
SELECT ID, color FROM <tablename> GROUP BY ID HAVING MIN(color) =
MAX(color);
Upvotes: 1
Reputation: 1271241
Use group by
and having
:
select id_no
from t
group by id_no
having min(color) = max(color);
Upvotes: 2