CODEWITHSUNDEEP
sql
A Manson
A Manson

Reputation: 11

sql match more than one row

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions