Select group by values depending on conditions

Question

I want to create a query when the result pick the minimum value if its starting with 2 and if it starts with 4 and no value starting with 2 then max of 4

Input:

value	fee
val_1	2001
val_1	4003
val_1	4002
val_2	4512
val_2	4186
val_3	4125
val_4	2058
val_5	2296

SELECT
value, 
CASE 
    When CAST (fee as varchar) like '1%' THEN min(fee)
    ELSE max(fee)
END as fee_sorted
FROM table_1
GROUP BY 1

Output:

val_1	2001
val_2	4512
val_3	4125
val_4	2058
val_5	2296

Answer 1

You can find minimum from 2s and maximum from 4s and then use coalesce:

-- sample data
WITH dataset(value, fee) AS (
    values ('val_1', 2001),
        ('val_1', 4003),
        ('val_1', 4002),
        ('val_2', 4512),
        ('val_2', 4186),
        ('val_3', 4125),
        ('val_4', 2058),
        ('val_5', 229)
)

-- query
select value,
    coalesce(
            min(if(CAST(fee as varchar) like '2%', fee)),
            max(if(CAST(fee as varchar) like '4%', fee))
        ) as fee
from dataset
group by value
order by value;

Output:

value	fee
val_1	2001
val_2	4512
val_3	4125
val_4	2058
val_5	229