Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: Query not returning correct results

    Hi, I am trying to return the last comment by projectid, when I run this query it is still giving me all comments instead of the last comment. What am I doing wrong?

    Please help.

    SELECT tblComments.PROJECTID, tblComments.COMMENT, Max(tblComments.LASTACT_DT) AS [Last Comment Date]
    FROM tblComments
    GROUP BY tblComments.PROJECTID, tblComments.COMMENT
    ORDER BY tblComments.PROJECTID, Max(tblComments.LASTACT_DT);

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    try

    SELECT top 1 tblComments.PROJECTID, tblComments.COMMENT
    FROM tblComments
    ORDER BY tblComments.PROJECTID DESC;
    ghozy.

  3. #3
    Join Date
    Jun 2004
    Posts
    3

    Query not returning correct results

    When I ran this query, this returned 1 result. I was actually looking for the max(date) by project id. So I should have the last comment for each different project. Can you suggest something else?

    try

    SELECT top 1 tblComments.PROJECTID, tblComments.COMMENT
    FROM tblComments
    ORDER BY tblComments.PROJECTID DESC;

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    sorry I didn't see the date field. basicaly what we do is sorting records DESCENDING by date and show the top one.

    SELECT TOP 1 tblComments.PROJECTID, tblComments.COMMENT,LASTACT_DT AS [Last Comment Date]
    FROM tblComments
    ORDER BY tblComments.LASTACT_DT DESC;
    ghozy.

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    ugh sorry I'm too slow today, I will give you correct the answer in a minute
    ghozy.

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    Should do the job. what I do is a using a subquery as a table, which finds maximum dates for each project ID then I JOIN this subquery with the real table by projectid and LASTACT_DT fields.

    SELECT MaxDates.PROJECTID, MaxDates.MaxOfLASTACT_DT, tblComments.COMMENT
    FROM (SELECT PROJECTID, Max(LASTACT_DT) AS MaxOfLASTACT_DT
    FROM tblComments
    GROUP BY PROJECTID
    ORDER BY PROJECTID) AS MaxDates
    INNER JOIN tblComments ON (MaxDates.MaxOfLASTACT_DT = tblComments.LASTACT_DT) AND (MaxDates.PROJECTID = tblComments.PROJECTID);
    ghozy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •