I am struggling with this query. I have a table deliverystatus with two fields, Email and Status. This table is populated with emails and their associated delivery statuses such as "enroute", "delivered", "no status", "undelivered". For a given email their can always be a status of "enroute" in addition to any other statuses mentioned. If an email has a status of "enroute" and no other statuses then only "enroute" should be selected, any other status should always supersede "enroute" and should be the only one reported. Check out the table below.
|Email | Status |
|---------- | ------------|
|abc@abc.com | enroute |
|abc@abc.com | delivered |
|xyz@abc.com | delivered |
|jkl@abc.com | undelivered |
|ppp@abc.com | enroute |
When I do this query, it should only get me the following. Notice abc@abc.com was only reported once with the "delivered" status as it superseded "enroute", whereas ppp@abc.com was listed with "enroute" since there was no other status found for that email.
|Email | Status |
|---------- | ------------|
|abc@abc.com | delivered |
|xyz@abc.com | delivered |
|jkl@abc.com | undelivered |
|ppp@abc.com | enroute |
can anybody assist please?
I assume you're dealing with no more than two status values and you're already using group by:
coalesce(max(case when status <> 'enroute' then status end), 'enroute')
Or
coalesce(max(nullif(status, 'enroute')), 'enroute')