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
----------------------------------------------
[email protected]     deferred    
[email protected]     delivered
[email protected]     processed
[email protected]             deferred  
[email protected]             processed 
[email protected]             bounced   
[email protected]         deferred
[email protected]         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:

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