Design select SQL query

Question

I have three values expected in a table case, Serious, Non-Serious, Unknown for each case_id

select case_id, case_seriousness 
from case;

I have to build a SQL query which should show one row per case_id.

If there are rows for a case_id with multiple values, then only one row should appear based on priority - Serious, Non-Serious then Unknown.

e.g. Serious is in one row rest of four rows have Non-Serious or Unknown then Serious will be he value to show in one record.

If there are records with Non-serious and Unknown then Non-Serious should appear.

So Priorities will be like from S, NS and UK

Answer 1

You can use the analytical function as follows:

select case_id, case_seriousness 
  from
      (select case_id, case_seriousness, 
              row_number() over (partition by case_id 
                                 order by case case_seriousness 
                                               when 'Serious' then 1 
                                               when 'Non-Serious' then 2
                                               else 3 
                                           end ) as rn 
          from case)
where rn = 1;

Alternatively, You can also use DECODE instead of CASE..WHEN