How to get min value from multiple columns for a row in SQL

Question

I need to get to first (min) date from a set of 4 (or more) columns.

I tried

select min (col1, col2, col3) from tbl

which is obviouslly wrong.

let's say I have these 4 columns

col1     |  col2     | col3  |  col4
1/1/17   | 2/2/17    |       | 3/3/17

... in this case what I want to get is the value in col1 (1/1/17). and Yes, these columns can include NULLs.

I am running this in dashDB

the columns are Date data type,

there is no ID nor Primary key column in this table,

and I need to do this for ALL rows in my query,

the columns are NOT in order. meaning that col1 does NOT have to be before col2 or it has to be null AND col2 does NOT have to be before col3 or it has to be NULL .. and so on

Answer 1

If your DB support least function, it is the best approach

select 

least 
(
nvl(col1,TO_DATE('2901-01-01','YYYY-MM-DD')),
nvl(col2,TO_DATE('2901-01-01','YYYY-MM-DD')),
nvl(col3,TO_DATE('2901-01-01','YYYY-MM-DD')),
nvl(col4,TO_DATE('2901-01-01','YYYY-MM-DD'))
)
from tbl

Edit: If all col(s) are null, then you can hardcode the output as null. The below query should work. I couldn't test it but this should work.

select 
case when 
    least 
    (
    nvl(col1,TO_DATE('2901-01-01','YYYY-MM-DD')),
    nvl(col2,TO_DATE('2901-01-01','YYYY-MM-DD')),
    nvl(col3,TO_DATE('2901-01-01','YYYY-MM-DD')),
    nvl(col4,TO_DATE('2901-01-01','YYYY-MM-DD'))
    ) 
    = TO_DATE('2901-01-01','YYYY-MM-DD') 
then null 
else 
    least 
(
nvl(col1,TO_DATE('2901-01-01','YYYY-MM-DD')),
nvl(col2,TO_DATE('2901-01-01','YYYY-MM-DD')),
nvl(col3,TO_DATE('2901-01-01','YYYY-MM-DD')),
nvl(col4,TO_DATE('2901-01-01','YYYY-MM-DD'))
) 
end 
as min_date
from tbl