Aggregation with column selecting element based on list of priorities

I would like to aggregate a list of elements where one column is the urgency. I would like to get a row for each item and take the highest “Urgency” observed for that item based on a list (or mapping).

Item	Urgency
A	Normal
A	Low
A	High
B	Normal
B	Low
C	High

Expected output:

Item	Urgency
A	High
B	Normal
C	High

The idea would be like aggregate doing a “max()”, but since the urgency is not numerical, I would need some kind of mapping.

Is there a way to do this in one step?

An alternative would be to create a CTE with a case when clause to map each urgency value to an integer. But if possible, I would prefer to do it without that additional step.

Answer

You can use max_by aggregate over grouping by Item with case statement to map urgency into numeric value:

-- sample data
WITH dataset (Item, Urgency) AS (
    VALUES ('A', 'Normal'),
        ('A', 'Low'),
        ('A', 'High'),
        ('B', 'Normal'),
        ('B', 'Low'),
        ('C', 'High')
) 

-- query
select Item,
    max_by(
        Urgency,
        case
            Urgency
            when 'Low' then 1
            when 'Normal' then 2
            when 'High' then 3
        end
    ) Urgency
from dataset
group by Item
order by Item

Output:

Item	Urgency
A	High
B	Normal
C	High