CODEWITHSUNDEEP
mysqlsql
DataDataData
DataDataData

Reputation: 19

Find distinct id by many row manipulations

Data set looks like this: 

ID  Rank   Case
1    1      1
1    2      0
1    3      0
2    1      0
2    2      1
2    3      0
3    1      1
3    2      0
3    3      0

I want to find all the IDs that has Rank=1 Case=0, Rank=2 Case=1, Rank=3 Case=0. In the above case, this would return ID2

Upvotes: 0

Views: 34

Answers (1)

juergen d
juergen d

Reputation: 204854

select id
from your_table
group by id
having sum(rank=1 and `case`=0) > 0
and sum(rank=2 and `case`=1) > 0
and sum(rank=3 and `case`=0) > 0

Upvotes: 1

Related Questions