--sample Data

declare @tbl table

(

id int

,field_1 date

,field_2 date

,field_3 date

)

insert into @tbl values(1,'20120823','20120824','20120825')

--Query

select max(a.mydate),a.id

from (

select id,field_1 mydate from @tbl

union all

select id,field_2 from @tbl

union all

select id,field_3 from @tbl

)a

group by a.id

I think this is what you require.... Please provide some sample day from next time.. So, that it will be easy to answer