Pubudu Dodangoda Pubudu Dodangoda - 22 days ago 8
MySQL Question

How to keep state in sql CASE WHEN expressions

I want to fetch the critical status of of errors from an SQL table which holds error information of some devices.

The critical status of an entry could be A , B or C. A is slightly critical, B is moderately critical and C is highly critical.

There could be multiple entries per a device. For example, as in following table.

DId | Critical Status
Device1 | A
Device2 | B
Device1 | C
Device3 | C
Device1 | B
Device4 | A


Now I want to get the maximum critical status against each device. From above example table, I want the following result

DId | Critical Status
Device1 | C
Device2 | B
Device3 | C
Device4 | A


Device Id's need NOT be in order. Any order is okay. I just want to get the most critical value against each device.

How can I write an sql query for that using mysql?

Edit - I saw some answers which suggest using the MAX function assuming that the critical status is in alphabetical order. I do get those answers. But is there a way to achieve the result if the strings are different. I.e - Open, InProgress and Resolved instead of C, B, A.

Answer
select      DId
           ,elt(max(field (CriticalStatus,'Open','InProgress','Resolved')),'Open','InProgress','Resolved')            

from        MyTable

group by    DId

or

select      DId
           ,substring(max(concat(case CriticalStatus when 'Open' then '1' when 'InProgress' then '2' when 'Resolved' then '3' end ,CriticalStatus)),2)     

from        MyTable

group by    DId

or

select      DId
           ,case max(case CriticalStatus when 'Open' then 1 when 'InProgress' then 2 when 'Resolved' then 3 end) 
                when 1 then 'Open' 
                when 2 then 'InProgress' 
                when 3 then 'Resolved' 
            end   

from        MyTable

group by    DId