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.
CodePudding user response:
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 |