Show column for the max value

Database

Show column for the max value

TD Version  15.10.1.4

 

Hello,

 

 

I saw on a previous example on how to get the column name for a max value but I have different question since my column names dont have a number suffix 

https://stackoverflow.com/questions/31821577/teradata-sql-max-greatest-2nd-and-3rd-greatest-column-n...

 

Here is my question 

I have a data set below and I want to know for ID =123 what is the column that produced the max value for all the columns.   So the max value in this row is 4,344.65  but I want the output to display:

ID    OUTPUT

123  2_YR_CLOTHES_CC_AMT

 

I want to know what category (column)  the customer spent the most in the last 2 years.   Thanks!

 

ID2_YR_AUTO_CC_AMT2_YR_BARS_CC_AMT2_YR_BLD_SPLY_WHLSALE_CC_AMT2_YR_CHRTY_CMUNITY_ORG_CC_AMT2_YR_CLOTHES_CC_AMT
1233,032.4650.283,999.21254,344.65

 


Accepted Solutions
Teradata Employee

Re: Show column for the max value

Seems like it's just a large CASE statement. You can leverage the GREATEST function to make it a bit less complicated to write - find the largest value, then figure out which column holds that value and return a character string with the name:

SELECT ID,
CASE GREATEST(2_YR_AUTO_CC_AMT,2_YR_BARS_CC_AMT,2_YR_BLD_SPLY_WHLSALE_CC_AMT,2_YR_CHRTY_CMUNITY_ORG_CC_AMT,2_YR_CLOTHES_CC_AMT)
WHEN 2_YR_AUTO_CC_AMT THEN '2_YR_AUTO_CC_AMT'
WHEN 2_YR_BARS_CC_AMT THEN '2_YR_BARS_CC_AMT'
WHEN 2_YR_BLD_SPLY_WHLSALE_CC_AMT THEN '2_YR_BLD_SPLY_WHLSALE_CC_AMT'
WHEN 2_YR_CHRTY_CMUNITY_ORG_CC_AMT THEN '2_YR_CHRTY_CMUNITY_ORG_CC_AMT'
WHEN 2_YR_CLOTHES_CC_AMT THEN '2_YR_CLOTHES_CC_AMT'
ELSE NULL END 
1 ACCEPTED SOLUTION
2 REPLIES 2
Teradata Employee

Re: Show column for the max value

Seems like it's just a large CASE statement. You can leverage the GREATEST function to make it a bit less complicated to write - find the largest value, then figure out which column holds that value and return a character string with the name:

SELECT ID,
CASE GREATEST(2_YR_AUTO_CC_AMT,2_YR_BARS_CC_AMT,2_YR_BLD_SPLY_WHLSALE_CC_AMT,2_YR_CHRTY_CMUNITY_ORG_CC_AMT,2_YR_CLOTHES_CC_AMT)
WHEN 2_YR_AUTO_CC_AMT THEN '2_YR_AUTO_CC_AMT'
WHEN 2_YR_BARS_CC_AMT THEN '2_YR_BARS_CC_AMT'
WHEN 2_YR_BLD_SPLY_WHLSALE_CC_AMT THEN '2_YR_BLD_SPLY_WHLSALE_CC_AMT'
WHEN 2_YR_CHRTY_CMUNITY_ORG_CC_AMT THEN '2_YR_CHRTY_CMUNITY_ORG_CC_AMT'
WHEN 2_YR_CLOTHES_CC_AMT THEN '2_YR_CLOTHES_CC_AMT'
ELSE NULL END 

Re: Show column for the max value

Thank you.  This is what I was looking for