SQL: Identifying which column has greatest value in each row
I have a table with columns ID, A, B, C, D, E... (10 numeric columns in all)
For each row, I need to find which column has the largest value, and what that value is.
开发者_C百科E.G. Here's 1 row of my table:
ID A B C D E F G H I J
XY 5 4 9 5 0 1 3 2 1 7
I want to generate 2 new columns:
maxvalue, which would equal 9, and maxcol, which would equal "C"Any advice, beyond a massive IF statement?
I don't have a sql processor to hand, but something along the lines of
select id , colName
from
(select id, 'A' as colName, a as value union all
select id, 'B' as colName, b as value union all
select id, 'C' as colName, c as value union all
select id, 'D' as colName, d as value union all
select id, 'E' as colName, e as value union all
select id, 'F' as colName, f as value union all
select id, 'G' as colName, g as value union all
select id, 'H' as colName, h as value union all
select id, 'I' as colName, i as value union all
select id, 'J' as colName, j as value)
group by id having max(value)
A solution using unpivot
assuming your table is named "TestTable":
WITH unpivoted as
(
SELECT * FROM TestTable
UNPIVOT
(
Val FOR Col IN (A, B, C, D, E, F, G, H, I, J)
) as u
),
maxvals as (
SELECT ID, max(val) as MaxVal
FROM unpivoted
GROUP BY ID
)
SELECT
TestTable.*,
MaxVals.MaxVal,
(SELECT top 1 Col
FROM unpivoted
WHERE unpivoted.Id = TestTable.ID and Val = MaxVals.MaxVal) as MaxCol
FROM
TestTable
JOIN maxvals on maxvals.id = TestTable.Id
精彩评论