I have a table like below
x
date | weekending | isapproved
2/17/2020 2/23/2020 0
2/18/2020 2/23/2020 1
2/19/2020 2/23/2020 0
2/20/2020 2/23/2020 0
2/27/2020 3/1/2020 1
2/26/2020 3/1/2020 1
2/28/2020 3/1/2020 1
3/4/2020 3/8/2020 0
3/5/2020 3/8/2020 1
3/5/2020 3/8/2020 1
now want to get weekending ‘3/1/2020’ since it have all 1’s in isApproved like this I want to get list of weekending having all 1’s in isApproved
Advertisement
Answer
You can use aggregation and filter with a having
clause:
select weekending
from mytable
group by weekending
having min(isapproved) = 1
This works because isapproved
only has 0/1
values. So condition min(isapproved) = 1
actually ensures that there is no 0
value.