Help with query

[rossco]

Hi. I am trying to work out a query for a single table but not having much success. I need to display the most recent record per date modified per committee. At this stage I have been experimenting with the 'max' of the date modified and also the max of the improvement ID which gives the most recent date and the highest improvement ID however the data and improvement ID are not of the same record.

Here is the SQL statement:

Code: Select all

SELECT dbo_Improvement.CommitteeID, Max(dbo_Improvement.DateModified) AS MaxOfDateModified, Max(dbo_Improvement.ImprovementID) AS MaxOfImprovementID
FROM dbo_Improvement
GROUP BY dbo_Improvement.CommitteeID
ORDER BY dbo_Improvement.CommitteeID DESC;

Thanks in advance

[HansV]

Use

SELECT * FROM dbo_Improvement WHERE DateModified = (SELECT Max(t.DateModified) FROM dbo_Improvement AS t WHERE t.ImprovementID = dbo_Improvement.ImprovementID)