How to select the max of different columns?

Database

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!

2 REPLIES
WAQ
Enthusiast

Re: How to select the max of different columns?

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);

Supporter

Re: How to select the max of different columns?

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.