Select rows based on values in query

Question

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.

Show actual data sample and explain better the logic. The explanation above does really make much sense. — PChemGuy
– PChemGuy, Commented Oct 11, 2022 at 19:56

DannySlor · Accepted Answer · 2022-10-11 19:59:39Z

1

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

answered Oct 11, 2022 at 19:59

DannySlor

4,6201 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select rows based on values in query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related