How do you get the latest data from a table...

[colezpapa]

I have a table with the following layout.
It is a table that stores comments. There will be multiple comments per fn. I have built several reports over the past few months where they want to see the latest comment.
They complain and have been correct that I am not giving them the latest comment. I did a group by where thought I was providing the latest comment. Is there a trick on doing this with this table layout.

Table
-----
Fn,text
Comment, text
CommentDate, date
UserId, text

 

[PHV]

Something like this (SQL code) ?
SELECT T.Fn, T.Comment, T.CommentDate, T.UserId
FROM yourTable AS T INNER JOIN (
SELECT Fn, Max(CommentDate) AS LatestDate FROM yourTable GROUP BY Fn
) AS L ON T.Fn = L.Fn AND T.CommentDate = L.LatestDate

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

 

[colezpapa]

Thank you, I will give it a try.