How to get maximum value across multiple columns in SQL row

Data and Databases
1

I’m working with a database table that has multiple columns containing numeric values. My table has columns named [LEVEL 1], [LEVEL 2], [LEVEL 3], up to [LEVEL 10], and I need to find the highest value among these columns for each row.

I’ve been trying to use PIVOT and UNPIVOT operations but can’t get them working properly. Right now I’m using a CASE statement approach like this:

SELECT *,
    CASE 
        WHEN [LEVEL 1] >= [LEVEL 2] AND [LEVEL 1] >= [LEVEL 3] THEN [LEVEL 1]
        WHEN [LEVEL 2] >= [LEVEL 1] AND [LEVEL 2] >= [LEVEL 3] THEN [LEVEL 2]
        WHEN [LEVEL 3] >= [LEVEL 1] AND [LEVEL 3] >= [LEVEL 2] THEN [LEVEL 3]
        ELSE NULL
    END AS [HIGHEST_LEVEL]
FROM employee_data

This works but it’s not scalable. If I add more level columns later, I’d have to rewrite the entire query. Is there a better way to find the maximum value across all these columns that would automatically handle additional columns?

2

your case statement approach works, but 10 columns will be painful. try the GREATEST function instead: SELECT GREATEST([LEVEL 1], [LEVEL 2], [LEVEL 3]...) AS max_level - works everywhere except SQL Server. which database are u using?

3

UNPIVOT’s your best bet here - it scales way better than hardcoding comparisons. Here’s what works for all ten columns:

SELECT employee_id, MAX(level_value) AS HIGHEST_LEVEL
FROM (
    SELECT employee_id, level_value
    FROM employee_data
    UNPIVOT (
        level_value FOR level_name IN 
        ([LEVEL 1], [LEVEL 2], [LEVEL 3], [LEVEL 4], [LEVEL 5], 
         [LEVEL 6], [LEVEL 7], [LEVEL 8], [LEVEL 9], [LEVEL 10])
    ) AS unpvt
) AS subquery
GROUP BY employee_id

It flips your columns to rows, runs MAX, then groups by employee. Need more level columns later? Just add them to the UNPIVOT list. Way cleaner than nested CASE statements and performs better with decent indexing.

4

Interesting problem! Have you tried UNPIVOT with a subquery? You could unpivot all the level columns into rows, then use MAX() with GROUP BY. What database system are u using? That’ll probably change the best approach.