Need to find a maximum or minimum value?

[Giraffe69]

Is there a quick way of getting the maximum or minimum of some columns?
ie. say I want to select the maximum of two columns I can write it as:

SELECT (CASE WHEN COL1>COL2 THEN COL1 ELSE COL2 END) AS RESULT
FROM etc...

Obviously the MAX function doesn't do this, ie

SELECT MAX(COL1,COL2) AS RESULT ...

wont work. Using CASE WHEN is OK when there's only two columns but when there's more or a combination of maximum and minimum it becomes a real pain.

Sorry if this is real beginner stuff but any help would be appreciated!

 

[BillDHS]

You could union the values of the columns, then select the max value, then go back and retrieve the base row. Whether this is quick or easy remains to be seen.

select t3.key, t3.cola, t3.colb, t3.colc,...
from tablea t3,
(select max(t1.value)
from
(Select colavalue
from tablea
union
Select colbvalue
from tablea
union
Select colcvalue
from tablea) t1(value)
) t2.(mvalue)
where
t3.cola = t2.mvalue
OR
t3.colb = t2.mvalue
OR
t3.colc = t2.mvalue;

 

[KevinKGillette]

I don't think Teradata has a magic function that works on a single row (MIN() and MAX() are GROUP BY functions), but assuming your list of columns is reasonably small, you can simulate the function in a CASE statement that's not too nasty.

Let's assume you have 4 commensurable fields, col1, col2, col3, and col4. To compute the maximum of the four on any row, you would code:

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 AS max_col_value

Note that the columns in this example maintain their order, and that each WHEN sub-clause has each inequality dominated (some might use the expression "majorized") by the first term. This is the generalizing principle, and suggests that you will always have as many WHEN- and ELSE- sub-clauses as you have fields to compare. So if you were finding the maximum of 6 fields, you would anticipate coding 6 lines within your CASE statement, including the ELSE sub-clause.

To compute minimum instead of maximum, simply reverse each inequality, and leave everything else identically as shown above.

Kevin Gillette

 

[teralearner]

select case when col1 > col2 then col1 else col2 end as max1,
case when col3 > max1 then col3 else max1 end as max_col

if you have 4 colums, you need to get max1(result from col1 and col2) and max2 (result form col3 and col4). Get max_col from max1 and max2.