select column name with maximum date from various column in a table

Question

I have a table with four columns with different dates.

I want to retrieve a column name with maximum date; can any one help on how to go about that?

Answer 1

I am not very familiar with oracle, so am avoiding SQL Server specific approaches (other than the table variable for sample purposes), but a way to solve the problem as stated would be to create a subquery that unions the four columns and maps column names, another subquery that retrieves the max for the 4 values, and an outer query that returns the joined result. The sample below will return the max for the table, but this could be adjusted to the max for row by modifying the aggregating subquery to group by a row identifier, and then joining on that column as well

Declare @x table(id int
, date1 datetime
, date2 datetime
, date3 datetime
, date4  datetime)

insert into @x  
values (1, '01/01/2000'
, '01/01/2001'
, '01/01/2002', '01/01/2004')

insert into @x  
values (2, '01/01/2010'
, '01/01/2020'
, '01/01/2030', '01/01/2040')


Select SomeDate, ColumnName
from
(
Select date1 as somedate
, 'Date 1' as ColumnName from @x
union  
Select date2 as somedate
, 'Date 2' as ColumnName from @x
union
Select date3 as somedate
, 'Date 3' as ColumnName  from @x
union 
Select date4 as somedate
, 'Date 4' as ColumnName from @x 
) t1
inner join
(
    Select max(tx.somedate) MaxDate from 
    (
     Select date1 as somedate from @x
     union  
     Select date2 as somedate from @x
     union
     Select date3 as somedate  from @x
     union 
     Select date4 as somedate from @x
    ) as tx 
) t2
on t1.SomeDate = t2.MaxDate

Answer 2

If, on a per row basis, I wanted the latest date in two date coluns, I'd use a case statement:

select case when dt1 > d2 then dt1 else dt2 end as latest from table;

If I wanted the column name (and I don't know why I'd want that):

 select case when dt1 > d2 then 'dt1' else 'dt2' end as latest from table;

If I wanted to deal with more columns, I'd nest more case statements:

select case when dt1 > d2 then 
     (case when dt1 > dt3 then dt1 else dt3 end ) 
   else 
     (case when dt2 > dt3 then dt2 else dt3 end ) 
   end as latest 
from table;

Or, better, I could do it like this:

 select case 
     when dt1 > d2 and dt1 > dt3 then dt1
     when dt2 > d1 and dt2 > dt3 then dt2
     else dt3 end as latest
 from table ;

Or (maybe better) I might write a function. Since max is a built-in fuction, I'd write something called maxOf. This is in MySQL's function syntax, your db may use a different syntax, and may use OUT variables instead of returning a value:

create function maxOf( date lhs, date rhs )
   RETURNS date
   LANGUAGE SQL
   DETERMINISTIC
   CONTAINS SQL
   COMMENT 'return the later of two dates'
   return case when lhs > rhs then lhs else rhs end;


select maxOf( dt1, maxOf( dt2, dt3) as latest from table;

Answer 3

Oracle has GREATEST and LEAST

SELECT GREATEST (TO_DATE ('01-01-2000', 'DD-MM-YYYY'), TO_DATE ('01-01-2010', 'DD-MM-YYYY')) FROM DUAL;

SELECT LEAST (TO_DATE ('01-01-2000', 'DD-MM-YYYY'), TO_DATE ('01-01-2010', 'DD-MM-YYYY')) FROM DUAL;