I need to be able to show when a file entered its current state (when it was last transitioned) to display the 'age' of the file in that state. Using SQL.

The following query (with document and state hard-coded for simplicity) works fine in most cases.

SELECT * FROM TransitionHistory TH2

INNER JOIN Transitions T ON TH2.TransitionID = T.TransitionID

WHERE TH2.DocumentID = 79360

AND T.Destination = 38

Sometimes, however, a file enters the same state multiple times (because of loops) and/or through multiple transitions (because of different paths through the loop).  In such cases the query returns multiple results for the same file.  I want to filter it so that it only shows the most recent transition into the current state.  I tried this:

SELECT * FROM TransitionHistory TH2

INNER JOIN Transitions T ON TH2.TransitionID = T.TransitionID

WHERE TH2.DocumentID = 79360

AND T.Destination = 38

AND TH2.Date = (SELECT MAX(TH2a.Date)

                    FROM TransitionHistory TH2a

                    WHERE TH2a.TransitionID = T.TransitionID

                    AND TH2a.DocumentID = 79360)                   

But it makes no difference, and I continue to get multiple results.  I've spent 3 hours combing through it and trying stuff (I tried using TransitionNr instead of Date) but I can't see any problem.  I hope someone can spot my (probably obvious) mistake and help me to discover the key to making this query work.

Thanks.