I am using a databound gridview to display information about ongoing projects. The data is primarily pulled from the tProjects table but associated with several other tables, one of which is tComments. There can be multiple comments per project. Currently the gridview is displaying one line for each project/comment pair so that if there are three comments on a project there are three lines displayed. I need to limit the display to 1 row with only the most recent comment. I've tried using TOP in several ways but can't seem to get it to work.
This is the SQL that displays 1 row per project/comment pair. How would I modify it to limit the display to the most recent comment?
SELECT tUsers.strUserName AS [Section Lead], tLocations.strLocation AS Location,
tProjects.strDWGNumber AS [Drawing Number], tProjectStatuses.strStatus AS Status,
tProjects.dteDueDate AS [Due Date], tProjects.intProjectID, tComments.strComment AS
Comment, tComments.dtmDateEntered AS [Comment Date]
FROM tProjects INNER JOIN tUsers ON tProjects.intUserID = tUsers.intUserID INNER JOIN
tProjectStatuses ON tProjects.intProjectStatusID = tProjectStatuses.intProjectStatusID
INNER JOIN tLocations ON tUsers.intLocationID = tLocations.intLocationID INNER JOIN
tComments ON tProjects.intProjectID = tComments.intProjectID
WHERE (tProjects.dteDueDate < @Today)
ORDER BY [Due Date], Location, [Comment Date] DESC