How i want make 3 rows of the same primary key into 1 row by checking and choose its value using case

For example here, from this table

key  | status
1001 |   A
1001 |   D
1001 |   C

the hierarchy will be C>D>A

If the the stats contain C as the value, the person status will become C in one row. It will be like this:

key  | status
1001 |   C

it will ignore D and A because it has value C. If it doesn’t has C,then it will check for D first before A. So, how can i do that? I dont how to make the 3 row into 1. I tried using

''' CASE WHEN STATUS IN('C')THEN 'C'
         ELSE WHEN STATUS IN('D') THEN 'D'
         ELSE WHEN STATUS IN('A') THEN 'A'
    END AS STATUS '''

But it give error and still can’t make the row into 1

Answer

You can try the below –

select key, status
from tablname
order by case when status='C' then 1 when status='D' then 2 when status='A' then 3 else 99 end 
FETCH FIRST 1 ROW