SQL - Get the index of column that has maximum value

Question

I have a table with sample data as below.

col1    col2    col3
4       6       9
7       1       5

I want to get the index of column that has value matches the maximum value on that row and If they are equal, just ignore the later.

For example, the result should be return

3 (because col3 has maximum value 9)
1 (because col1 has maximum value 7)

Please note that the number of columns is undefined, so I need a general solution.

Thank you

Answer 1

A more general solution (i.e. N columns) to this is to Unpivot the columns into rows, and then a windowing function can be applied to obtain the group wise maximum to each set of column 'rows'. You will however need some kind of key for each row, so that the maximum can be applied in row wise fashion (to allow reassembling the original rows). I've done this by adding a surrogate Guid via newId(). Note this returns the column NAME with the highest value in each row:

WITH MyTableWithRowId AS
(
    SELECT newId() AS Id, *
    FROM MyTable
),
Unpivoted AS
(
    SELECT Ndx, Id, col, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY col DESC) AS Rnk
    FROM 
    MyTableWithRowId tbl
    UNPIVOT
    (
      col for Ndx in(col1, col2, col3)
    ) p
)
SELECT Ndx
FROM Unpivoted
WHERE Rnk = 1

SqlFiddle here

Edit, re just '1, 2, 3' not the name of the column (col1, col2, col3)

As per @Giorgi's comment, if you really want the (one based) ordinal position of the column in each row, you can join back into DMV's such as INFORMATION_SCHEMA.COLUMNS to look up the ordinal, although this would be terribly fragile strategy IMO.

WITH MyTableWithRowId AS
(
    SELECT newId() AS Id, col1, col2, col3
    FROM MyTable
),
TheOrdinalPositionOfColumns AS
(
    SELECT COLUMN_NAME, ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MyTable'
),
Unpivoted AS
(
    SELECT Ndx, Id, col, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY col DESC) AS Rnk
    FROM 
    MyTableWithRowId tbl
    UNPIVOT
    (
      col for Ndx in(col1, col2, col3)
    ) p
)
SELECT topoc.ORDINAL_POSITION AS ColumnOrdinalPosition
FROM Unpivoted
JOIN TheOrdinalPositionOfColumns topoc ON Unpivoted.Ndx = topoc.COLUMN_NAME
WHERE Rnk = 1;

Updated Fiddle with Giorgi's Column naming

Answer 2

You can do it like this:

select case 
           when col1 >= col2 and col1 >= col3 then 1
           when col2 >= col1 and col2 >= col3 then 2
           when col3 >= col1 and col3 >= col2 then 3
        end as ColIndex
from table