Select first matching string value in list from SQL table

Question

I'd like to query Table and return the most granular frequency in the table for a given row. Sample table and desired result are below. I've tried a few iterations of the query but haven't cracked it yet.

By "most granular frequency" I mean that I'd like to return the first match for any row in this set ['hourly', 'daily', 'weekly', 'monthly'] as a new column called min_frequency

Table

----------------------------------
id     | name          | frequency
----------------------------------
----------------------------------
1      | apples        | hourly
----------------------------------
2      | apples        | daily
----------------------------------
3      | oranges       | weekly
----------------------------------
4      | oranges       | monthly
----------------------------------

Desired result:

---

name          | min_frequency
----------------------------------
----------------------------------
apples        | hourly
----------------------------------
oranges       | weekly
----------------------------------

Current attempt:

SELECT name, (
  CASE 
   WHEN frequency='hourly' then frequency
   WHEN frequency='daily' then frequency
   WHEN frequency='weekly' then frequency
   WHEN frequency='yearly' then frequency
  END
) as min_frequency from Table
GROUP BY name, min_frequency

Answer 1

You could use distinct on with conditional sorting logic:

select distinct on (name) *
from mytable
order by 
    name, 
    case frequency
        when 'hourly'  then 1
        when 'daily'   then 2
        when 'weekly'  then 3
        when 'monthly' then 4
    end