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).
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.
You can use
max_by aggregate over grouping by
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