Select rows based on values in query-CodePudding

My table :

id	role
b	f
b	s
b	g
a	s
a	f
c	f

I want a distinct id but with corresponding role, with this logic:

If g exists select g if not 
if s exists select s if not 
if f exists select f.

Query should yield :

id	role
b	g
a	s
c	f

I tried group by id, but role cannot be sorted in a useful order.

CodePudding user response：

select  id
       ,role
from    (
        select  *
                ,rank() over(partition by id order by case when role = 'g' then 2 when role = 's' then 1 end desc) as rnk
        from    t
        ) t
where   rnk = 1

id	role
a	s
b	g
c	f

Fiddle