选择最新记录

首先按日期时间排列记录

     SELECT *,RANK() OVER (PARTITION BY student_id ORDER BY Recored_timestamp desc) as ranking
     FROM #temp

然后，如果你想知道student_id不为空的最新记录，那么你可以使用OUTER APPLY添加一个非NULL的student_id列。

OUTER APPLY (SELECT student_id 
             FROM #temp 
             WHERE #temp.teacher_id = ranktable.teacher_id 
               AND student_id IS NOT NULL
            ) AS jointable   

这是一个例子：

create table #temp
(
     teacher_id int,student_id int,teacher_name varchar(40),comment varchar(100),Recored_timestamp datetiR_81_11845@e
)

INSERT INTO #temp 
VALUES
 (449,111,'lucy','Could be better','2021-05-04 07:00:00.000'),(449,null,'smily','2021-05-11 07:00:00.000'),'not listening','2021-05-08 07:00:00.000'),(448,'Toni','Good','2021-06-04 09:00:00.000'),222,'not doing as expected','2021-06-04 08:00:00.000')

SELECT DISTinCT teacher_id,jointable.student_id,teacher_name,comment,Recored_timestamp,ranking
FROM
(
     select *,RANK() OVER (PARTITION BY teacher_id ORDER BY Recored_timestamp desc) AS ranking
     FROM #temp
) AS ranktable
OUTER APPLY (SELECT student_id 
             FROM #temp 
             WHERE #temp.teacher_id = ranktable.teacher_id 
               AND student_id IS NOT NULL
            ) AS jointable   
WHERE ranking = 1 --only neWest record will be extracted

drop table #temp

使用窗口函数，按teacher_id和student_id进行分区，然后按recorded_timestamp排序，就会得到想要的结果。

SELEct * from(SELEct teacher_id,student_id,recorded_timestamp,row_number() over(partition by teacher_id,student_id order by recorded_timestamp desc)as rownum from temp0607)out1 where rownum=1

此外，您可能还需要查看 Recorded_timestamp 的存储方式。如果存储为字符串，则可以使用 from_unixtime(unix_timestamp(recorded_timestamp,'dd/MM/yyyy HH:mm'),'dd/MM/yyyy HH:mm')

您可以根据此查询获取最新数据。

SELECT TOP 1 * FROM tablename T1 INNER JOIN(SELECT teacher_id,Max(Recored_timestamp) as MaxDate from tablename GROUP BY teacher_id) T2 ON T2。 teacher_id = T1.teacher_id AND T1.Recored_timestamp = T2.MaxDate