Reputation: 11
i'm not sure of the correct terms to describe this issue correctly so my search came up with many results due to common terms.
i have a table that looks like this:
id status
1 finished
1 finished
1 finished
1 skipped
2 skipped
2 skipped
2 finished
2 finished
3 finished
3 skipped
3 finished
3 skipped
4 skipped
4 skipped
4 skipped
4 skipped
5 finished
5 finished
5 skipped
5 skipped
6 finished
6 finished
6 skipped
6 skipped
i want to return the id from the first column (id) if the values in all 4 rows containing that id have "skipped" in the second column (status). if only 1 or 2 or 3 contain skipped, then not interested.
so, for the table above, the result will be: 4
Upvotes: 1
Views: 38
Reputation: 1271241
You can use group by
and having
:
select id
from t
group by id
having min(status) = max(status) and min(status) = 'skipped';
This does not check for exactly four rows. If that is important add and count(*) = 4
to the having
clause.
Upvotes: 2