SELECT
  store,
  ELT(
    MAX(CASE status WHEN 'OK'       THEN 1
                    WHEN 'Warning'  THEN 2
                    WHEN 'Critical' THEN 3
                                    ELSE 4 END),
    'OK',
    'Warning',
    'Critical',
    'Unknown'
  )
    AS final_status
FROM
  yourTable
GROUP BY
  store

你可以用 ELT 以及 conditional aggregation :

SELECT Store, ELT(MAX(CASE WHEN Status='OK' THEN 1
                       WHEN status='Warning' THEN 2
                       WHEN status='Critical' THEN 3 END)
            , 'OK', 'Warning', 'Critical') AS output
FROM tab
GROUP BY Store;

dbfiddle演示

也可以使用相关方法：

select Store, (select t1.Status
               from table t1
               where t1.Store = t.Store
               order by (case when t1.Status = 'Critical' 
                              then 1  
                              when t1.Status = 'Warning' 
                              then 2
                              when t1.Status = 'OK' 
                              then 3
                         end)
                limit 1 
               ) as Status  
from table t
group by Store;