with cte
  as (select *
        from (select *
                    ,row_number() over(partition by [student id] order by [date of visit] desc) as rn
                    ,count(*) over(partition by [student id]) as c
                from dbo.students
             ) as a
       where c>1
        and rn<3
     )
select l.[student id] as id
      ,l.[date of visit] as [latest visit]
      ,l.detail as [latest detail]
      ,p.[date of visit] as [previous visit]
      ,p.detail as [previous detail]
  from cte as l
       inner join cte as p
               on p.[student id]=l.[student id]
              and p.rn=l.rn+1