How to print out only most recent comment from a Work Order database

[hpavay]

Goal: Print out each work order with most recent comment only.

I have the following so far, but I can only get the most recent "COMMENTDATE" to print, along with each work order.

When I try using "dbo_comments.sys_comment" in the sub query, I receive an ERROR(This is the field name of the comment).

SELECT dbo_request.sys_assignedto, dbo_request.sys_requestdate, dbo_request.sys_problemdesc, dbo_request.sys_requesttype_id, dbo_request.sys_solutiondesc, dbo_request.usr_lastname, dbo_request.sys_requestclosedate, (SELECT TOP 1 dbo_comments.sys_commentdate
FROM dbo_comments
Where dbo_request.sys_request_id = dbo_comments.sys_request_id
Order by dbo_comments.sys_comment) AS Expr1
FROM dbo_request


WHERE (((dbo_request.sys_assignedto)=[Enter Analyst ID]) AND ((dbo_request.sys_requestclosedate) Is Null));

[kennejd]

Give this a try....you have some problems with your SQL. I changed the layout a little, but it should still work:

SELECT
d.sys_assignedto
, d.sys_requestdate
, d.sys_problemdesc
, d.sys_requesttype_id
, d.sys_solutiondesc
, d.usr_lastname
, d.sys_requestclosedate
,(SELECT top 1 c.sys_request_id, c.sys_comment
FROM dbo_comments c
WHERE c.sys_request_id = d.sys_request_id
Order by c.sys_commentdate DESC)
FROM dbo_request d


WHERE
(((d.sys_assignedto)=[Enter Analyst ID]) AND ((d.sys_requestclosedate) Is Null));