Friday, 27 September 2013

MySQL Latest Timestamp + Other Field with Group By

MySQL Latest Timestamp + Other Field with Group By

I have a table that stores events and timestamps as they relate to tasks
being performed. A "report" task is a collection of "tests," each with its
own set of events and associated timestamps.
The structure is as follows:

reportID
testID
eventDateTime
eventType
Each eventType is something like "Start", "Pause", "Finish", etc. What I'm
trying to do is write a query that will tell me the last action taken on a
given reportID/testID combination and its timestamp.
My preliminary query is:

SELECT reportID, MAX(eventDateTime), eventType
FROM testtracker_event
GROUP BY reportID, testID
The result is very close to what I want, and I'm getting the latest
eventDateTime (which I want), but it's returning the first eventType (as
opposed to the eventType associated with the most recent timestamp.)
I realize similar questions have been asked, but I've searched and
searched and this seems much simpler than any similar questions I've
found. I want to believe this is possible without a subquery or join but
I'm getting the idea based on answers to similar questions with more
complicated logic that it's not.

No comments:

Post a Comment