Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

http://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
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
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.