SQL Distinct by ID and Latest By Date

Question

I have the following SQL Statement. I need to select the latest record for each System.Id using the System.RevisedDate

SELECT     [System.Id],[System.RevisedDate], [System.Title], [System.State], [System.Reason], [System.CreatedDate], [System.WorkItemType], [System.TeamProject], 
                      [Microsoft.VSTS.Scheduling.RemainingWork], [Microsoft.VSTS.Scheduling.CompletedWork], [Microsoft.VSTS.CMMI.Estimate]
FROM         WorkItems
WHERE     ([System.WorkItemType] = 'Change Request') AND ([System.CreatedDate] >= '09/30/2008') AND ([System.TeamProject] NOT LIKE '%Deleted%') AND 
                      ([System.TeamProject] NOT LIKE '%Sandbox%')

Can you please help?

Answer 1

In general it should go some thing like

SELECT ID,DATE_FIELD,FIELD1,FIELD2
FROM TBL1 AS A WHERE DATE_FIELD >= ALL (
   SELECT DATE_FIELD FROM TBL1 AS B
   WHERE A.ID = B.ID 
)

Answer 2

or, (using subquery)

SELECT ID,DATE_FIELD,FIELD1,FIELD2
FROM TBL1 T
Where DATE_FIELD = 
    (Select Max(Date_Field) From Tbl1 
     Where Id = T.Id)

but I couldn't decipher your example query... Is "System" a table? Why are there no references to WorkItem in the Select clause? it doesn't seem to jibe together...

Answer 3

Try this:

SELECT * FROM WorkItems w
JOIN (
SELECT     [System.Id],MAX([System.RevisedDate])
FROM WorkItems
WHERE ([System.WorkItemType] = 'Change Request') 
AND ([System.CreatedDate] >= '09/30/2008') 
AND ([System.TeamProject] NOT LIKE '%Deleted%') 
AND ([System.TeamProject] NOT LIKE '%Sandbox%')
GROUP BY {System.Id]
) x ON w.[System.Id] = x.[System.Id] AND w.[System.DateRevised] = x.[System.DateRevised]

Answer 4

Try

SELECT [System.Id],[System.RevisedDate], [System.Title], [System.State], [System.Reason], [System.CreatedDate], [System.WorkItemType], [System.TeamProject], 
[Microsoft.VSTS.Scheduling.RemainingWork], [Microsoft.VSTS.Scheduling.CompletedWork], [Microsoft.VSTS.CMMI.Estimate]
FROM         WorkItems
Left JOIN (Select [System.Id],Max([System.RevisedDate]) group by [System.Id]) LatestItems
    on Workitems.[System.Id] = LatestItems.[System.Id] and 
    Workitems.[System.RevisedDate] = LatestItems.[System.RevisedDate]
    WHERE     
([System.WorkItemType] = 'Change Request') AND ([System.CreatedDate] >= '09/30/2008') AND ([System.TeamProject] NOT LIKE '%Deleted%') AND 
                          ([System.TeamProject] NOT LIKE '%Sandbox%')

Answer 5

I like Alex's answer, but thought I'd throw in another possibility. Sorry, didn't get a chance to test it. :-)

SELECT     WorkItems.[System.Id], WorkItems.[System.RevisedDate], [System.Title], [System.State],     [System.Reason], [System.CreatedDate], [System.WorkItemType], [System.TeamProject], 
                  [Microsoft.VSTS.Scheduling.RemainingWork], [Microsoft.VSTS.Scheduling.CompletedWork], [Microsoft.VSTS.CMMI.Estimate]
FROM         WorkItems join (SELECT     [System.Id],max([System.RevisedDate]) [MaxDate]
       FROM         WorkItems
       WHERE     ([System.WorkItemType] = 'Change Request') AND ([System.CreatedDate] >= '09/30/2008') AND ([System.TeamProject] NOT LIKE '%Deleted%') AND 
                         ([System.TeamProject] NOT LIKE '%Sandbox%')
       group by [System.Id]) MaxDates
on  WorkItems.[System.Id] = MaxDates.[System.Id]
and WorkItems.[System.RevisedDate] = MaxDates.[MaxDate]