Getting distinct list of records based on the MAX of a column 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.
This answer was originally posted by the OP as an edit to the question; it is now reposted as a Community Wiki answer.



I was able to find an answer with some more searching in the archive.

https://stackoverflow.com/questions/1049702/create-a-sql-query-to-retrieve-most-recent-records

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