Select statement searching on date criteria

[polo]

Hello everyone!

I've been banging my head of a brick wall with this one. Its probably straight forward... but I can't see the solution! A problem shared is a problem halved... or so they say!

Ok I have this table called CustomerComments. In it are the following columns

CustID
Comment
CommentDate

For each custID I want the most recent commentdate and its matching comment from that row... (there can be multilple comments recorded per custID)

Problem is I can't seem to get this to work.. instead its returning all rows. I have run a query that will sort and group this list. The top line of the grouping gives the most recent comment and commentdate per customer.

Does anyone know how I could just get that top line per custID rather than all rows returned?

Thanks for reading this... hope it makes sense!

 

[FoFa]

Select TOP 1 from MyTable
WHERE CustID = xxxxx
Order By CommentDate DESC

 

[polo]

Select TOP 1 from MyTable
WHERE CustID = xxxxx
Order By CommentDate DESC

Hi FoFa
I'm getting an error using that Select syntax "reserver word mispelt" and it hightlights the "from" is "Top 1" correct?

Also if I wanted it for all how could I specify that? rather than just one custID?

Thanks for your help!!

 

[Jon K]

You can do it with a series of two queries.

qryOne:-
SELECT CustID, Max(CommentDate) AS MaxOfCommentDate
FROM CustomerComments
GROUP BY CustID;

qryTwo:-
SELECT CustomerComments.CustID, CustomerComments.Comment, CustomerComments.CommentDate
FROM CustomerComments INNER JOIN qryOne ON (CustomerComments.CustID = qryOne.CustID) AND (CustomerComments.CommentDate = qryOne.MaxOfCommentDate);

Run the second query.
.

 

[polo]

Thank you so much Jon K! That works a gem!