February 17, 2005 at 4:37 pm
Does anyone know of a good efficient way of finding the max value among the columns for each row. Ex I have a table carrying Account Nbr and say 50 more columns representing 50 diff scores for that account.
AcctNbr Score1 Score2 score3 ...... Score50
I need to find out for each account number Which score column has the highest value, second highest value and the third highest value.
February 17, 2005 at 4:43 pm
This is why it is never a good idea to denormalise and treat a relational record like an array of repeating values.
By denormalising, you've lost the benefit of SQL aggregates of SETS of data.
So,
SELECT AcctNbr, Max(Score)
FROM
(
SELECT AcctNbr, Score1 As Score
FROM YourTable
UNION
SELECT AcctNbr, Score2 As Score
FROM YourTable
UNION
SELECT AcctNbr, Score3 As Score
FROM YourTable
...
UNION
SELECT AcctNbr, Score50 As Score
FROM YourTable
) vtNormalise
GROUP BY AcctNbr
The finger-numbing exercise of creating all the required UNIONs is all yours.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply