user6512519 user6512519 - 5 months ago 16
SQL Question

How to select a higher value from 3 three different fields using a case statement

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.

Answer

You can achieve what you want with a CASE expression:

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 low, medium, and high.