Select max date from different columns for a single row

Question

I have this data in a table.

create table pick_max_date
(
 student_id int
,date1 datetime
 ,date2 datetime
 ,date3 datetime
 ,date4 datetime
) 

insert into pick_max_date
(student_id,date1,date2,date3,date4)values
(1,'2015-06-01','2016-01-01','2014-01-01','2017-01-01')
,(2,'2016-06-01','2017-08-01','2018-01-01','2017-05-06')
,(3,'2013-06-01','2019-08-01','2012-01-01','2012-05-06')

select * from pick_max_date

I need to select the max date for each student as below.

    student_id    max_date
    ----------   ----------
       1          2017-01-01
       2          2018-01-01
       3          2019-08-01

what is the most optimised way to select as above. any help is appreciated.

Thanks in advance

Answer 1

Construct a derived table of the columns and select the max from that table,

select student_id,
       (
       select max(d.d)
       from (values(date1),
                   (date2),
                   (date3),
                   (date4)
            ) as d(d)
       ) as max_date
from dbo.pick_max_date;

Answer 2

Sergey Gegoyan describes in the following link very efficient four different ways to solve this problem. I would prefer the first one the most:

LINK

Let me know if you found it useful.

Answer 3

SELECT student_id ,
  (SELECT Max(v)    FROM (VALUES (date1), (date2), (date3)) AS value(v)) as maxdate
FROM pick_max_Date

or

SELECT student_id,b.*
from
pick_max_Date
cross apply
(select max(d)
from
(values(date1),(date2),(date3),(date4))v(d)
)b(maxx)

Answer 4

You can do either the values tables already mentioned, or simply use a case if the columns are static:

select student_id

        ,(select max(d)
            from (values(date1),(date2),(date3),(date4)) as tbl(d)) as MaxDate

        ,case when date1 >= date2 and date1 >= date3 and date1 >= date4 then date1
              when date2 >= date1 and date2 >= date3 and date2 >= date4 then date2
              when date3 >= date1 and date3 >= date2 and date3 >= date4 then date3
              else date4
              end as MaxDate2
from pick_max_date