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      |
|----------  | ------------|
|[email protected] | enroute     |
|[email protected] | delivered   |
|[email protected] | delivered   |
|[email protected] | undelivered |
|[email protected] | enroute     |

When I do this query, it should only get me the following. Notice [email protected] was only reported once with the "delivered" status as it superseded "enroute", whereas [email protected] was listed with "enroute" since there was no other status found for that email.

|Email       | Status      |
|----------  | ------------|
|[email protected] | delivered   |
|[email protected] | delivered   |
|[email protected] | undelivered |
|[email protected] | enroute     |

can anybody assist please?

CodePudding user response：

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')