Return Last Record by Project 2

[MajP]

I need a query to return the last Comment made on each Project. Here is my table
tblProjectComments
intProjectID (foriegn key)
memoComment (the comment)
dtmDateOfComment (the date of the comment)

Thanks.

 

[DotNetGnat]

SELECT A.intProjectID ,A.memoComment , A.dtmDateOfComment FROM tblProjectComments A
WHERE A.dtmDateOfComment IN ( SELECT TOP 1 dtmDateOfComment FROM tblProjectComments B WHERe A.intProjectID =B.intProjectID ORDER BY dtmDateOfComment DESC)

-DNG

 

[PHV]

Another way:
SELECT A.intProjectID, A.memoComment, A.dtmDateOfComment FROM tblProjectComments A INNER JOIN (
SELECT intProjectID, Max(dtmDateOfComment) As LastDate FROM tblProjectComments GROUP BY intProjectID
) As B ON A.intProjectID = B.intProjectID AND A.dtmDateOfComment = B.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Thanks.