How to write a SQL script to get desired output

Question

Table

Member| Mum_Status
mem1  | active
mem2  | future
mem3  | active
mem3  | future

How to write a query to get below output from above table

• Future records only needs to populate when member is not currently active
• If member have both active and future status then future record should not be populate

Desired output:

Member| Status
mem1  | active
mem2  | future
mem3  | active

Answer 1

Do a GROUP BY. Use MIN to pick active (if available), otherwise future is returned.

select member, min(status)
from tablename
group by member

Will work very well as long as no other status types are introduced.

Alternatively, NOT EXISTS:

select member, status
from tablename t1
where status = 'active'
   or NOT EXISTS (select * from tablename t2
                  where t2.member = t1.member
                    and t1.status = 'active')

If a row has active status, return it. Otherwise it has future status, return it in case that no other row for same member has active status.