how to get maxmum/minimum number between multiple columns in a row

Database

how to get maxmum/minimum number between multiple columns in a row

Hi, guys

I have rows like below:

id  s_Jan s_Feb s_Mar s_Apr

1   100    102    108    99

2    80     105    30      70

I  need to get result as below

id    s_max  s_min

1    108        99

2    105        33

I only list 4 columns, actully the data have 12 months, I don't think it is a good idea to write a case when sql statement like

case when (s_Jan <s_feb and s_Jan <s_Mar and s_Jan <s_Apr) then s_Jan

.....

It will make me crazy. :-(

Any feedback is appreciate.

11 REPLIES
Enthusiast

Re: how to get maxmum/minimum number between multiple columns in a row

I think you can transpose the columns into rows and then use simply MIN and MAX Window functions.

Khurram

Re: how to get maxmum/minimum number between multiple columns in a row

Thanks Khurram for your feedback

This is one potential method, but maybe there are some way better. :-)

Enthusiast

Re: how to get maxmum/minimum number between multiple columns in a row

Hi,

You can take union all of all the fields so that they are in a row and then take min and max.

Note : Just an example ::::::::::::::select sjan from yy where id=1 union all..select sfeb from yy where id=1... to make it to column level. and you can generalize it as much as you can .

Cheers,

Raja

Teradata Employee

Re: how to get maxmum/minimum number between multiple columns in a row

If you have many queries like that, you might even want to consider changing your table structure.   I.e., transposing columns into rows, which is what both Khurram and Raja are suggesting to do for the query.   It would provide you with more flexibility to use aggregate functions.

Regards,

Vlad.

Junior Contributor

Re: how to get maxmum/minimum number between multiple columns in a row

You didn't specify your TD release.

In TD14 there's a GREATEST/LEAST, but it's only defined up to 10 columns, so you need to nest it:

GREATEST(GREATEST(col11,col2,col3,col4,col5,col6),
GREATEST(col7,col8,col9,col10,col11,col12))

Otherwise the CASE will drive you crazy, but it's the most efficient way. At least it can be simplified:

CASE
WHEN col1 <= col2 AND col1 <= col3 AND col1 <= col4 THEN col1
WHEN col2 <= col3 AND col2 <= col4 THEN col2
WHEN col3 <= col4 THEN col3
ELSE col4
END

Dieter

Re: how to get maxmum/minimum number between multiple columns in a row

CREATE VOLATILE TABLE dt AS (

SEL CURRENT_DATE AS dt1, CURRENT_DATE-INTERVAL '30' DAY AS dt2, CURRENT_DATE-INTERVAL '60' DAY AS dt3

)WITH DATA PRIMARY INDEX(dt1, dt2, dt3) ON COMMIT PRESERVE ROWS;
  
SEL LEAST(dt1, dt2, dt3) FROM dt; 

Does this function work with dates?  I've tried on several table and get an error for inalid number of parameters.  Here's an example:

Re: how to get maxmum/minimum number between multiple columns in a row

Hi Dieter,

 

My DB version is 

VERSION 15.10.04.03c
RELEASE 15.10.04.03

But when i am trying to use LEAST / GREATEST function with more than 2 parameters i get the error 9881. The function works well for 2 parameters. 

 

SELECT Failed. 9881:  Function 'GREATEST' called with an invalid number or type of parameters 

Is this something which has been changed in the higher versions or should I be raising a case with T@YS..

Teradata Employee

Re: how to get maxmum/minimum number between multiple columns in a row

It works with up to 10 parameters.  Check the data types of all the arguments: if they are not the same then you will get this error message.  You can probably re-cast one or some of them so they all match.

Re: how to get maxmum/minimum number between multiple columns in a row

Thanks for your response. 

 

The date column has been casted to integer, please see below two sample queries:

 

SEL CAST(
LEAST(
CAST((DATE+1) AS INT),
CAST((DATE) AS INT),
CAST ((DATE - 2) AS INT)
) AS DATE);

Error: SELECT Failed. 9881:  Function 'LEAST' called with an invalid number or type of parameters 

 

SEL CAST(
LEAST(
CAST((DATE+1) AS INT),
CAST((DATE) AS INT)
) AS DATE);

Output: 6/7/2017