SQL: Select the minimum value from multiple columns with null values

Question

I have a table like this one

ID   Col1   Col2   Col3
--   ----   ----   ----
1      7    NULL    12  
2      2     46    NULL
3     NULL  NULL   NULL
4     245     1    792

I wanted a query that yields the following result

 ID   Col1   Col2   Col3  MIN
 --   ----   ----   ----  ---
  1     7    NULL    12    7
  2     2     46    NULL   2
  3    NULL  NULL   NULL  NULL
  4    245    1     792    1

I mean, I wanted a column containing the minimum values out of Col1, Col2, and Col 3 for each row ignoring NULL values. In a previous question (What's the best way to select the minimum value from multiple columns?) there is an answer for non NULL values. I need a query as efficient as possible for a huge table.

Select Id,
       Case When Col1 < Col2 And Col1 < Col3 Then Col1
            When Col2 < Col1 And Col2 < Col3 Then Col2 
            Else Col3
            End As MIN
From   YourTableNameHere

Answer 1

Assuming you can define some "max" value (I'll use 9999 here) that your real values will never exceed:

Select Id,
       Case When Col1 < COALESCE(Col2, 9999)
             And Col1 < COALESCE(Col3, 9999) Then Col1
            When Col2 < COALESCE(Col1, 9999) 
             And Col2 < COALESCE(Col3, 9999) Then Col2 
            Else Col3
       End As MIN
    From YourTableNameHere;

Answer 2

You didn't specify which version of Teradata you're using. If you're using version 14+ then you can use least.

Unfortunately least will return null if any of its arguments are null. From the docs:

LEAST supports 1-10 numeric values. If numeric_value is the data type of the first argument, the return data type is numeric. The remaining arguments in the input list must be the same or compatible types. If either input parameter is NULL, NULL is returned.

But you can get around that by using coalesce as Joe did in his answer.

select id, 
  least(coalesce(col1,9999),coalesce(col2,9999),coalesce(col3,9999))
from mytable