Setting rules for max() with strings

Each record in my table has a random selection of these strings, so it looks like

record   fruit
A        pear
A        apple_guava
A        pear_guava
A        mango_guava
B        mango
C        apple_guava
C        pear_guava

I want to get the highest value of “fruit” for each record, except I want it to be sorted lower if it contains the string guava. This is the output that I’d like and I’m using Snowflake SQL

output:

record   fruit
A        pear
B        mango
C        apple_guava

Currently, I’m trying to do this by assigning every possible value of fruit a number with a case when statemnet, using max on that list and the converting the result back into a string. That won’t work in my case because the names of the fruit will change, so I don’t want a hard coded list

this is what I’ve tried

select
   record,
   left(max(case when fruit ilike '%guava%' then z || fruit else fruit end), 1) as fruit
from table
group by 1 

Answer

Desired results with Snowflake (as tagged):

• array_agg() can be ordered
• for the order we can sort first by guava existing, and then by fruit.
• [0] obtains the first element of array_agg().

with data as (
    select split(value, '        ')[0]::string record, split(value, '        ')[1]::string fruit
    from table(split_to_table(
'A        pear
A        apple_guava
A        pear_guava
A        mango_guava
B        mango
C        apple_guava
C        pear_guava'
    , 'n'))
)           

select record
  , (array_agg(distinct fruit) 
     within group (
         order by contains(fruit, 'guava'), fruit
     ))[0]::string max_fruit
from data
group by record