CODEWITHSUNDEEP
sqlsql-server
Jeff
Jeff

Reputation: 2313

Get greatest value between columns and associated column name

I'm looking to get the largest value in a set of columns, as well as the column name. For example, given:

| Id | A | B | C |
------------------
| 1  | 1 | 2 | 3 |

I would expect:

| Id | Column | Value |
------------------------
| 1  |    C   |   3   |

I've already gotten the part of the query to determine the greatest value, but I'm struggling to display what column it came from as well. 

SELECT Id,
(SELECT Max(v) 
FROM (VALUES (A), (B), (C)) AS value(v)) as MaxValue
FROM [dbo].[MyTable]

I feel like I'm really close, but I'm not sure how to finish this off. Thanks!

Upvotes: 2

Views: 80

Answers (1)

Dan
Dan

Reputation: 5231

One way (not claiming it's the best way) to attack your problem is to rank your column values, and then select what we want from that data set.

First to unpivot your record(s):

select id
       , columnName
       , columnValue
from mytable
     unpivot
     (
        columnValue for columnName in(a,b,c)
     ) as unpvt

Next, we can assign a ranking to the values based on what we want to see output. To rank the largest column value for an ID 1st, we add:

select id
       , columnName
       , columnValue
       , rank() over (partition by id order by columnValue DESC, columnName DESC) as rankVal
    from mytable
    unpivot
    (
        columnValue for columnName in(a,b,c)
    ) as unpvt

Note that above in our rank(), if we order just by columnValue, you would end up with two rank 1s if two columns had the same max value. The next step would then return two records for an ID. If this is the output you would want to see, remove the , columnName DESC from the rank() order by.

Now that we have our values ranked, we can select what we want from that result set:

with cteUnpivot(id, columnName, columnValue, rankVal)
AS
(
    select id
           , columnName
           , columnValue
           , rank() over (partition by id order by columnValue DESC, columnName DESC) as rankVal
    from mytable
    unpivot
    (
        columnValue for columnName in(a,b,c)
    ) as unpvt
)

select id
       , columnName as [Column]
       , columnValue as [Value]
from cteUnpivot
where rankVal = 1

Upvotes: 2

Related Questions