SQL Server distinct仅选择最新日期

因此,我有一个独特的查询,可以将列表缩小到我想要的内容,但有时最后一个表可能有3个正确的选择,而我只想要最新出现的内容。

当前查询:

SELECT DISTCINT
    D.Filename, S.Name AS State, W.Name AS 'Workflow Name', U.Username AS 'Name', 
    CONVERT(VARCHAR, TH.Date, 101) AS [Entered State], 
    DATEDIFF(dd, TH.Date, GETUTCDATE()) AS [Days In State]
FROM
    Status AS S 
INNER JOIN
    Documents AS D ON D.CurrentStatusID = S.StatusID 
INNER JOIN
    Workflows AS W ON W.WorkflowID = S.WorkflowID 
INNER JOIN 
    TransitionHistory AS TH ON D.DocumentID = TH.DocumentID 
                            AND D.LatestRevisionNo - 1 = TH.RevNr 
INNER JOIN
    Users AS U ON U.UserID = TH.UserID
WHERE
    d.Filename NOT LIKE '%test%' 
    AND d.filename NOT LIKE '%^%' 
    AND S.Name IN ('Initiated', 'Design Review', 'Change Pending Approval',
                   'Under Editing', 'Waiting for Approval', 'Under Change',
                   'ECO Design Review', 'Minor Change no Revision',)
    AND W.Name IN ('Production')
ORDER BY
    Filename

例如,如果我选择一个特定文件(请参见图),您可以看到结果:

您可以在上一个表中看到,结果生成了3行符合条件的行,我试图找到一种简单的方法使其与我的所有条件匹配,然后只输出最高的TransitionNr或最新的日期。

我不是一个喜欢SQL的人,我从谷歌搜索中收集了这些信息,但我似乎找不到最后一块。

谢谢你的帮助。