CODEWITHSUNDEEP
sql
sparkless
sparkless

Reputation: 305

How to find devices that have all values of specific column?

I have a table like this;

deviceId, status, time

status column only maybe active, idle, alarm. I want to find all deviceIds that have all status values.

So, if one device has active, idle and alarm value in the status column on that table, I want it's id.

How can i do this?

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can use group by and having. If you know there are three values, just do:

select deviceId
from t
group by deviceId
having count(distinct status) = 3;

If you don't know the number of values in the column, use a subquery to calculate them:

select deviceId
from t
group by deviceId
having count(distinct status) = (select count(distinct status) from t);

If you want those three in particular, add a where clause:

select deviceId
from t
where status in ('active', 'idle', 'alarm')
group by deviceId
having count(distinct status) = 3;

Upvotes: 1

Related Questions