Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

enter image description here

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 
        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
share|improve this question
You should move the solution you found into an answer, and accept that answer. Thanks. – Jon Seigel Sep 14 '13 at 20:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.