I have a column of ID’s and a column of strings. In reality these strings are ordinal however, so when grouping by the ID I would like some method of selecting the maximum of the custom order.

For example say I have

such that banana < pear < apple, or apple = 3, pear = 2, banana = 1.

Then I would group by ID selecting the maximum string for each ID according to this ordering.

Select
ID,
max_ordinal(Str)
from table

giving

I have tried putting the order into a separate table of strings and values and joining on, but this becomes a mess where I have to join, select max of the value, then re join the string associated.