I have a status table (tblTestActionStatus) which has three columns
ID_TestAction: references some test action
ID_Status: a look up table reference for the variety of possible statuses for the action
StatusDateTime: a datetime field which logs the exact time a status update was made for the test action.
So while a test action is happening, periodic status updates are made.
I am interested to know the current status which would be the last status update of a test action as of the moment I run the query. For example, test action # 100 has gotten 4 status changes so far. The first was 1, then 2, then 3, and most recently 4. And test action # 101 has gotten 3 status updates so far. So I would like to write a query that returns for me all the columns in the table but for only the most recent StatusDateTimes.
I attached a pic which shows the table contents and the rows I would like to see coming back from the query highlighted.
I was able to find an answer with some more searching in the archive.
So the SQL I created from that post which works in my case is as follows...
SELECT tblTestActionStatus.ID_TestAction, StatusDateTime, ID_Status FROM tblTestActionStatus INNER JOIN ( Select MAX(StatusDateTime) as LatestDate, ID_TestAction FROM tblTestActionStatus Group By ID_TestAction ) SubMax on tblTestActionStatus.StatusDateTime = SubMax.LatestDate and tblTestActionStatus.ID_TestAction = SubMax.ID_TestAction