Returning newest entry for items with multlple entries

[GoKats78]

OK...I have a document control database where there are multiple revisions of the same document stored in the "dbo.EdmObject" table. The only valid field that I can use to pull to most recent revision is the "dbo_EdmObject.LastModified" field. Unfortunately I am at a loss how to out the reqeust in the is code...
Any help would be appreciated.





SELECT dbo_EdmFileItem.FileItemID, dbo_EdmFileItem.FileName, dbo_EdmObject.ObjectDescription,
dbo_EdmObjectAttributes.VAL_2, dbo_EdmObject.ObjectVersion, dbo_EdmObjectDef.ObjectDefName,
dbo_EdmFileItem.FileUnqFilename, Right([FileUnqFilename],5) AS [Last 5], dbo_EdmFileItem.LastModified,
dbo_EdmObject.LastModified, dbo_EdmObjectAttributes.ObjectDefID
FROM (((dbo_EdmFileItem INNER JOIN dbo_EdmFileItemToObj_Vw ON dbo_EdmFileItem.FileItemID = dbo_EdmFileItemToObj_Vw.FileItemID)
INNER JOIN dbo_EdmObject ON dbo_EdmFileItemToObj_Vw.ObjectID =
dbo_EdmObject.ObjectID) INNER JOIN dbo_EdmObjectAttributes ON dbo_EdmObject.ObjectID = dbo_EdmObjectAttributes.ObjectID)
INNER JOIN dbo_EdmObjectDef ON dbo_EdmFileItemToObj_Vw.ObjectDefID = dbo_EdmObjectDef.ObjectDefID
WHERE (((dbo_EdmObjectAttributes.ObjectDefID)=8))
ORDER BY dbo_EdmObject.LastModified DESC;

[GoKats78]

I've cleaned up the the query a bit...



SELECT dbo_EdmFileItem.FileName, dbo_EdmObject.ObjectDescription, dbo_EdmObject.LastModified
FROM (dbo_EdmFileItem INNER JOIN dbo_EdmFileItemToObj_Vw ON dbo_EdmFileItem.FileItemID = dbo_EdmFileItemToObj_Vw.FileItemID)
INNER JOIN dbo_EdmObject ON dbo_EdmFileItemToObj_Vw.ObjectID = dbo_EdmObject.ObjectID
WHERE (((dbo_EdmObject.ObjectDefID)=8))
GROUP BY dbo_EdmFileItem.FileName, dbo_EdmObject.ObjectDescription, dbo_EdmObject.LastModified
ORDER BY dbo_EdmFileItem.FileName, dbo_EdmObject.LastModified DESC;

[OBP]

You need to use the Max Function in the Grouping, but to get it to work you may have to to create that part in a separate query and refer to in your one.
This is using the Max function
SELECT Budget.Customer, Max(Budget.Date) AS MaxOfDate
FROM Budget
GROUP BY Budget.Customer;