SQL - get column index with maximum value

Question

I have a table with sample data as shown below.

col1 col2 col3 4 6 9 7 1 5 

I want to get the index of a column whose value matches the maximum value in this row, and if they are equal, just ignore it later.

For example, the result should be returned

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

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

thanks

Answer 1

A more general solution (i.e., N columns) is to unpivot the columns into rows, and then you can use the window function to obtain the group maximum value for each set of columns of the "row". However, you will need some kind of key for each row, so the maximum can be applied in different ways (to allow the assembly of the source rows). I did this by adding a Guid surrogate via newId() . Note that this returns the NAME column 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

Change, just enter "1, 2, 3" not the column name (col1, col2, col3)

According to @Giorgi's comment, if you really want a (one base) column position in each row, you can join the DMV, for example INFORMATION_SCHEMA.COLUMNS , to find the sequence number, although that would be a terribly fragile IMO strategy.

 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 Giorgi column names script

Answer 2

You can do it as follows:

 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 

Answer 3

Try something like this

 select case when col1 >= col2 and col1 >= col3 then 1 when col2 >= col1 and col2 >= col3 then 2 else 3 end as [index] from myquestion_table 

See DEMO HERE

Answer 4

Try the following:

 select case when col1 >= col2 and col1 >= col3 then 1 when col2 >= col1 and col2 >= col3 then 2 else 3 end as ind from mytable 

Answer 5

This is a very simple example, but it will be something like this:

 select case when col1 > col2 and col1 > col3 then col1 when col2> col3 then col2 else col3 end as greatestColumn from table 

Answer 6

Try this without a fulcrum.

- You can add N number of columns.

 CREATE TABLE Table1 ([id] int primary key identity(1,1),[col1] int, [col2] int, [col3] int) ; INSERT INTO Table1 ([col1], [col2], [col3]) VALUES (4, 6, 9), (7, 1, 5) ; 

---

 DECLARE @tempTable as table(name varchar(50),maxValue int) DECLARE @maxColumn int SELECT @maxColumn = max(ordinal_position) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Table1' DECLARE @maxRow int SELECT @maxRow = Count(col1) FROM Table1 DECLARE @rowCounter int = 1 DECLARE @colCounter int = 1 DECLARE @columnName varchar(max) DECLARE @colValue varchar(max) DECLARE @q nvarchar(max) DECLARE @maxValue int DECLARE @ParmDefinition nvarchar(500) DECLARE @FinalResult table (id int, columnName nvarchar(max)) DECLARE @rowId int WHILE(@rowCounter <= @maxRow) BEGIN WHILE (@colCounter <= @maxColumn) BEGIN SELECT @columnName = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Table1' and ordinal_position = @colCounter --select @columnName,@rowCounter,@colCounter SELECT @q = 'select @retvalOUT =' + @columnName + ' from Table1 where id = ' + cast(@rowCounter as NVARCHAR) SET @ParmDefinition = N'@retvalOUT int OUTPUT'; EXEC sp_executesql @q,@ParmDefinition ,@retvalOUT = @maxValue OUT --select '@maxValue' + @maxValue INSERT INTO @tempTable VALUES (@columnName,@maxValue) SET @colCounter = @colCounter + 1 END SELECT @rowId = maxValue FROM @tempTable WHERE name LIKE 'id' -- Primary key column INSERT INTO @FinalResult(id,columnName) SELECT TOP 1 @id,name FROM @tempTable WHERE name not like 'id' ORDER BY maxvalue DESC DELETE FROM @tempTable --select * from @FinalResult SET @colCounter = 1 SET @rowCounter = @rowCounter + 1 END SELECT * FROM @FinalResult 

Answer 7

Here is another swivel solution, it is slightly shorter than another swivel solution:

 DECLARE @t table (col1 int, col2 int, col3 int) INSERT @t SELECT 4,8,9 union all SELECT 7,1,5 ;WITH addrownumber AS ( SELECT rn = row_number() over (order by (select 1)), * FROM @t ) , unpiv AS ( SELECT rn, value, colname, ordinalposition = row_number() over (partition by rn order by (select 1)), rn2 = row_number() over (partition by rn order by value DESC, rn) FROM addrownumber as p UNPIVOT (value FOR colname IN ([col1], [col2], [col3])) AS unpvt -- since you need all columns to be mentioned in pivot, you can set up -- the ordinal order here, by putting in columns in the right order. ) SELECT ordinalposition, value, colname FROM unpiv WHERE rn2 = 1 

Result:

 ordinalposition value colname 3 9 col3 1 7 col1