MIN from the list of fields in the record

Question

I have a table with three date fields. I am trying to find the smallest of the three dates in each record.

What I would like to do is:

Select id, some_value, date_a, date_b, date_c, min(date_a, date_b, date_c) as smallest_date from some_table; 

but this clearly does not work. There is no rule that states which date may be greater than others, and sometimes any number of them (not for everyone) may be NULL. I'm sure I saw an elegant solution for this, where there weren't a lot of ugly case statements and checked for null, but I just can't remember how to do this.

(Oracle 10g)

Answer 1

You need the LEAST function:

 SELECT LEAST(date_a, date_b, date_c) as smallest_date FROM some_table; 

AFAIK, if some of them may be empty, then you will need to use NVL for each column to set the default value:

 SELECT LEAST(NVL(date_a,sysdate), NVL(date_b,sysdate), NVL(date_c,sysdate)) as smallest_date FROM some_table;