select column name from max query

Question

I have a query that goes something like this :

 ;WITH t as
(
select 1 as RowNumber, 1 as ObjectID, 10 as [Col1],  20 as [Col2],  20 as [Col3],  20 as [Col4] UNION ALL
select 2 as RowNumber, 2 as ObjectID, 20 as [Col1],  30 as [Col2],  40 as [Col3],  50 as [Col4]
)
SELECT  RowNumber, ObjectID,
        (
        SELECT  MAX(Amount)
        FROM    (
                SELECT  [Col1] AS Amount
                UNION ALL
                SELECT  [Col2]
                UNION ALL
                SELECT  [Col3]
                UNION ALL
                SELECT  [Col4]
                ) d
       WHERE   Amount > 0
        )
FROM    t

The query works fine, but I want to know is where the Max(Amount) comes from.

So in my result set, on top of having (RowNumber, ObjectId, Amount) I want the name of the column (Col1, Col2, Col3, Col4) as a String.

Is there any way to do that?

EDIT Question from the comments : If two columns have the same max, it could be either one? Yes, it could be either one. Any column name will do as long as I know where it could be coming from.

Using SQL Server 2008

Answer 1

Don't MAX: use TOP which avoids the aggregate/GROUP BY.

It can also deal with duplicates using WITH TIES

I'm not sure if what you had was psuedo-code or a sub-query, but this should do what you want

 SELECT TOP 1 -- WITH TIES if needed
     *
 FROM
     (
     SELECT  RowNumber, ObjectID, [Col1] AS Amount, 'Col1' AS ColName
     FROM table
     UNION ALL
     SELECT  RowNumber, ObjectID, [Col2], 'Col2' AS ColName
     FROM table
     UNION ALL
     SELECT  RowNumber, ObjectID, [Col3], 'Col3' AS ColName
     FROM table
     UNION ALL
     SELECT  RowNumber, ObjectID, [Col4], 'Col4' AS ColName
     FROM table
     ) foo
 WHERE   Amount > 0
 ORDER BY Amount DESC

Your main problem is that you'll have to touch the table 4 times no matter how you do it because a subquery only returns one value. I can't see a ROW_NUMBER solution either (but there probably is one though... :-)

Answer 2

This is untested: however to see whats going on with your data, this might help. Not really production code quality:

SELECT  RowNumber, ObjectID,
        (
        SELECT  MAX(Amount)
        FROM    (
                SELECT str([Col1]) + ", col1, " AS Amount
                UNION ALL
                SELECT str([Col2]) + ", col2" 
                UNION ALL
                SELECT str([Col3]) + ", col3"
                UNION ALL
                SELECT str([Col4]) + ", col4"
                )
        WHERE   Amount > 0
        )
FROM    table

str() is the "toString()" function of your DBMS. Your SQL seems pretty weird, what DBMS are you using?