只获取最新的时间戳条目mysql

我有一个查询为报表提取数据。它当前提取所有记录,但我被要求创建它,以便它只从每个SID的changereport表中提取最新条目。

    select *, old.methodName as oldName, new.methodName as newName, students.firstName as fName, students.lastName as lName
from changeReport 
left join methodLookup as old on (old.methodID = changeReport.oldMethod) 
left join methodLookup as new on (new.methodID = changeReport.newMethod) 
join students on (students.studentID = changeReport.studentID) 
left join staffaccounts on (changeReport.staffID = staffaccounts.staffID) 
where 31 IN (newSubMethod,oldSubMethod) AND date(timestamp) = CURRENT_DATE

如何提取相同的报告,但只显示每个SID的最新时间戳?每个SID每天可能有1-10个条目……但我只想提取最新的条目。

我试过引用其他几个最好的N-per-group帖子,但似乎找不到解决这个特定问题的方法。

服务器类型:Mariadb 服务器版本:5.5.60-mariadb-mariadb服务器协议版本:10

SELECT a.* FROM ( SELECT * , o.methodName oldName , n.methodName newName , s.firstName fName , s.lastName lName FROM changeReport r LEFT JOIN methodLookup o ON o.methodID = r.oldMethod LEFT JOIN methodLookup n ON n.methodID = r.newMethod JOIN s s ON s.sID = r.studentID LEFT JOIN staffaccounts a ON r.staffID = a.staffID WHERE 31 IN (newSubMethodm,oldSubMethod) AND DATE(timestamp) = CURRENT_DATE ) a JOIN ( SELECT s.sid , MAX(timestamp) timestamp FROM changeReport r LEFT JOIN methodLookup o ON o.methodID = r.oldMethod LEFT JOIN methodLookup n ON n.methodID = r.newMethod JOIN s s ON s.sID = r.studentID LEFT JOIN staffaccounts a ON r.staffID = a.staffID WHERE 31 IN (newSubMethodm,oldSubMethod) AND DATE(timestamp) = CURRENT_DATE GROUP BY s.sid ) b ON b.sid = a.sid AND b.timestamp = a.timestamp;