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

jarlh · Accepted Answer · 2016-02-09 08:37:12Z

5

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.

edited Feb 9, 2016 at 8:37

answered Feb 9, 2016 at 8:32

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write a SQL script to get desired output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related