CODEWITHSUNDEEP
sql
SharpObject
SharpObject

Reputation: 83

SQL select rows with constant column value per ID

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

Answers (2)

nish
nish

Reputation: 1221

Hope this helps.

SELECT ID, color FROM <tablename> GROUP BY ID HAVING MIN(color) = 
MAX(color);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use group by and having:

select id_no
from t
group by id_no
having min(color) = max(color);

Upvotes: 2

Related Questions