All Forums Database
tdouglas 4 posts Joined 07/12
02 Jul 2012
How to select the max of different columns?

I'm still new to TeraData, so most of my issues and inquiries are probably simple.  I've done a few searches, and can't seem to find much...

If I have 5 columns, itm_cd1 thru itm_cd5, is there an easy way to select the highest value of the 5?

CASE WHEN (itm_cd1>=itm_cd2 thru itm_cd5 THEN itm_cd1 ELSE (CASE WHEN (itm_cd2>=itm_cd1,itm_cd thru itm_cd 5 THEN itm_cd2 ELSE...etc.

I haven't actually tried that--but is there a shorter/simpler way?

Many thanks!

WAQ 158 posts Joined 02/10
02 Jul 2012

One way to do this is:

select max(main.vals)
from (
 select max(itm_cd1) from table
 union all
 select max(itm_cd2) from table
 union all
 select max(itm_cd3) from table
 union all
 select max(itm_cd4) from table
 union all
 select max(itm_cd5) from table
) main (vals);

ulrich 816 posts Joined 09/09
03 Jul 2012

There is no elegant way below 14 and you need to take care of the data types but in case you are on 13.10 a SQL udf can limit the uglyness of the code.

REPLACE FUNCTION yourdb.colmax (i1 decimal(18,8), i2 \
decimal(18,8))
RETURNS decimal(18,8)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
Case when i1 > i2 then i1 else i2 end;

select yourdb.colmax(yourdb.colmax(yourdb.colmax(yourdb.colmax(1,6.5),3),5),4);

Where you can define a second SQL udf to deal with the 5 columns.

In TD 14 the function Greatest is introduced. 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.