CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Robert Dickey
Robert Dickey

Reputation: 834

Getting the latest timestamp entry only MYSQL

I have a query that pulls data for a report. It currently pulls all records, but I have been asked to make it so that it only pulls the latest entry from the changeReport table for each sID

    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

How can I pull the same report, but only show the latest timestamp only for each sID? each sID may have anywhere from 1-10 entries per day..but I would only like to pull the latest one.

I've tried referencing several other greatest-n-per-group posts but can't seem to find a solution for this particular issue.

Server type: MariaDB Server version: 5.5.60-MariaDB - MariaDB Server Protocol version: 10

Upvotes: 0

Views: 52

Answers (1)

Strawberry
Strawberry

Reputation: 33945

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;

Upvotes: 1

Related Questions