• I was thinking this could work

    ;WITH MySample AS (

    SELECT 101 AS Record_id, 'A' AS Col1, 'A' AS Col2, 'B' AS Col3, 'A' AS Col4, 'A' AS Col5 UNION ALL

    SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL

    SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL

    SELECT 104, 'D', 'C', 'D', 'D', 'D' UNION ALL

    SELECT 105, 'AA', 'A', 'A', 'A', 'A' UNION ALL

    SELECT 106, 'B', 'C', 'A', 'AA', 'D')

    SELECT Record_id, Newcol

    FROM Mysample

    CROSS APPLY (

    SELECT Newcol = CASE WHEN MAX(Newcol) = 'AA' THEN 'AA' WHEN MAX(Newcol) = MIN(Newcol) THEN MAX(Newcol) ELSE NULL END

    FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol)

    ) d

    WHERE Newcol IS NOT NULL

    Which will return 105, but not 106

    Wierd that 'AA' is not larger than 'D' The problem here is your using min/max functions on Strings which does not work for this situation.

    If you have the ability to influence the design now you should consider changing to Numeric values in these tables.

    Consider this simple test, which returns B as the max for Test 2 and not the significantly longer string of A's

    From BOL

    For character columns, MAX finds the highest value in the collating sequence.

    sqlSELECT Test, MIN(Val) AS MinVal, MAX(Val) AS MaxVal

    FROM (SELECT 1 AS Test, 'A' AS Val UNION ALL

    SELECT 1 AS Test, 'B' AS Val UNION ALL

    SELECT 1 AS Test, 'C' AS Val UNION ALL

    SELECT 2 AS Test, 'A' AS Val UNION ALL

    SELECT 2 AS Test, 'AAAAAAAAAA' AS Val UNION ALL

    SELECT 2 AS Test, 'B' AS Val) t

    GROUP BY Test