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?

Answer 3

Adding a step to user202553's answer

;WITH t1 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],  50000045 as [Col4]
),
t2 as(
SELECT  RowNumber, ObjectID,
        (
        SELECT  TOP 1 CAST(C AS BINARY(4)) + CAST(Amount as BINARY(4)) 
        FROM    (
                SELECT 'Col1' AS C, [Col1] AS Amount
                UNION ALL
                SELECT 'Col2' AS C, [Col2]
                UNION ALL
                SELECT 'Col3' AS C, [Col3]
                UNION ALL
                SELECT 'Col4' AS C, [Col4]
                ) d
       WHERE   Amount > 0
       ORDER BY Amount desc
        ) AS Top1
FROM    t1
)
SELECT RowNumber, 
       ObjectID,
       CAST(Left(Top1, 4) AS CHAR(4)) AS Col, 
       CAST(SUBSTRING(Top1,5,4) AS INT) AS Amount
FROM t2

Answer 4

You can use a combination of UNPIVOT and OUTER APPLY:

;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,
  ColName,
  ColAmount
FROM t
OUTER APPLY (
  SELECT TOP 1 
    ColName,
    ColAmount
  FROM 
    (
      SELECT
        Col1,
        Col2,
        Col3,
        Col4
    ) x 
    UNPIVOT (
      ColAmount FOR ColName IN (Col1, Col2, Col3, Col4) 
    ) y
   WHERE ColAmount > 0
   ORDER BY ColAmount DESC
 ) z

Results:

RowNumber   ObjectID    ColName   ColAmount
----------- ----------- --------- -----------
1           1           Col2      20
2           2           Col4      50