I have 3 fields (field_1, field_2, field_3) all the have the same potential value (high, medium, low). I want to write a case statement that would select the higher value of the 3 fields and put it in field_4. The higher value being in the obvious order high, medium, low. Any help would be very appreciated.
You can achieve what you want with a
SELECT field_1, field_2, field_3, CASE WHEN field_1 = 'high' OR field_2 = 'high' OR field_3 = 'high' THEN 'high' WHEN field_1 = 'medium' OR field_2 = 'medium' OR field_3 = 'medium' THEN 'medium' WHEN field_1 = 'low' OR field_2 = 'low' OR field_3 = 'low' THEN 'low' END AS field_4 FROM yourTable
This assumes that there are no
NULL values in any of the original three
field columns and that the only values present are