All Forums Database
robertskin 4 posts Joined 02/12
30 Oct 2013
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.

M.Saeed Khurram 544 posts Joined 09/12
30 Oct 2013

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

Khurram

robertskin 4 posts Joined 02/12
31 Oct 2013

Thanks Khurram for your feedback
 
This is one potential method, but maybe there are some way better. :-)

Raja_KT 1246 posts Joined 07/09
31 Oct 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

VBurmist 96 posts Joined 12/09
31 Oct 2013

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.

dnoeth 4628 posts Joined 11/04
01 Nov 2013

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
 

Dieter

christophjones 3 posts Joined 01/12
14 Oct 2015
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:
 
 
 

You must sign in to leave a comment.