SQLTeam.com | Weblogs | Forums

MAX value from different columns but excluding a specific number

Hi there,
I need to pick the largest value from 4 columns but excluding the number 6.
Say in each of these columns, the values only go from 1-6.
If all 4 columns are =6 then, I populate 6
Otherwise, I need to populate the high value apart from 6.

E.g.
6, 6, 6, 6, then I need 6
6, 2, 6, 3, then I need 3
1, 6, 6, 6, then I need 1

For the first scenario I thought of something like:
CASE WHEN column1 = 6 AND column2 = 6 and column3 = 6 AND column4 = 6 THEN 6
But I have no idea how to formulate the rest.
How should I achieve this please?

Hope it makes sense (sorry first time using the forum)
Would be great if someone could help :slight_smile:
Thank you!!


;WITH test_data AS ( 
    SELECT *
    FROM ( VALUES(6, 6, 6, 6), (6, 2, 6, 3), (1, 6, 6, 6) ) AS 
        data(column1, column2, column3, column4 )
)
SELECT td.*, ISNULL(ca1.max_col, 6) AS max_value
FROM test_data td
CROSS APPLY (
    SELECT MAX(CASE WHEN cols.col <> 6 THEN cols.col END) AS max_col
    FROM ( VALUES(td.column1), (td.column2), (td.column3), (td.column4) ) AS cols(col)
) AS ca1