How to sort strings based off a dictionary of values?

[fre]

Hi & thanks in advance for reading,

I have a table as follows:

email                              event
----------------------------------------------
I-got-delivered@example.com     deferred    
I-got-delivered@example.com     delivered
I-got-delivered@example.com     processed
I-bounced@example.com             deferred  
I-bounced@example.com             processed 
I-bounced@example.com             bounced   
Im-processing@example.com         deferred
Im-processing@example.com         processed

where the events are ordered as follows:

{
    1: 'deferred',
    2: 'processed'
    3: 'bounced',
    4: 'delivered'
}

I want group by the email, compare the events and return only the max value for event (i.e. deferred < processed < bounced < delivered). The table should look like this:

I-got-delivered@example.com     delivered
I-bounced@example.com           bounced 
Im-processing@example.com       processed

I was thinking I could do it with lots of nested if statements, but I was wondering if there's a more elegant way to do it. How would you achieve this?

Thanks,
fre

[somesoni2 · Accepted solution]

Give this a try

your current search giving above table with field email, event
| replace "deferred" with 1 "processed" with 2 "bounced" with 3 "delivered" with 4 in event
| stats max(event) as event by email 
| replace "1" with "deferred" "2" with "processed" "3" with "bounced" "4" with "delivered" in event