CODEWITHSUNDEEP
sqloracle-databaseplsqlsubqueryunpivot
Sebastian Hubard
Sebastian Hubard

Reputation: 163

How to Identify the column with the max value per row

I have a table that looks like the below:

--------------------------------
ENTITY  |  VALUE X  |  VALUE Y  
--------------------------------
AAA     |    100    |    50     
--------------------------------
BBB     |     0     |    20      
--------------------------------
CCC     |    null   |    null   
--------------------------------
DDD     |    100    |    100    
--------------------------------

I want to identify which of the columns (X or Y) has the largest value (in my actual data set there are five column but I'm using two here for simplicity).

In the event of both being null, I would like the result to be null.

In the event of a tie, I would like to have a set rank for tie breakers (x > y > z...)

The desired output would look like the one below

---------------------------------------------------
ENTITY  |  VALUE X  |  VALUE Y  |  DESIRED OUTPUT  
---------------------------------------------------
AAA     |    100    |    50     |         X        
---------------------------------------------------
BBB     |     0     |    20     |         Y          
---------------------------------------------------
CCC     |    null   |    null   |       null       
---------------------------------------------------
DDD     |    100    |    100    |         X        
---------------------------------------------------

I keep trying to use MAX() however, I think this is mainly meant for comparing rows within a column, not the opposite...

Any help would be greatly appreciate. Thank you!

Upvotes: 1

Views: 175

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You can do this with case logic. Assuming none of the values are NULL:

select t.*,
       (case greatest(x, y)
            when x then 'x'
            when y then 'y'
        end) 
from t;

Upvotes: 1

GMB
GMB

Reputation: 222512

If you have more than two columns as explained in the question, then the most scalable approach uses a lateral join. The idea is to unpivot the columns to rows, filter out null values, then order by descending value, and keep the top row only:

select t.*, x.*
from mytable t
outer apply (
    select col
    from (
        select 'X' col, t.value_x val from dual
        union all select 'Y', t.value_y from dual
    ) x
    where x.val is not null
    order by x.val desc
    fetch first 1 row only
) x

Demo on DB Fiddlde:

ENTITY | VALUE_X | VALUE_Y | COL 
:----- | ------: | ------: | :---
AAA    |     100 |      50 | X   
BBB    |       0 |      20 | Y   
CCC    |    null |    null | null
DDD    |     100 |     100 | X

Upvotes: 1

Related Questions