SQL Select Max Date with Multiple records

Question

I am struggling with a query to pull most recent entries. I have a Notes table that contains the following columns:

BusinessDate
ReportGuid
NoteGuid
Note
NoteDate
NoteAddedBy

The BusinessDate, ReportGuid and NoteGuid are the PK on the table. This table allows a specific ReportGuid to have multiple notes per day. I have another table that contains additional Report info that will be joined and displayed for the users. I am trying to pull and display only the most recent note entry for each ReportGuid.

I tried using Max(NoteDate) but that is only getting me the latest note added to the table not the latest note for each ReportGuid.

Any help would be appreciated.

Thanks

UPDATE:

thanks for the help:

SELECT N.Note, N.ReportGuid
FROM Tracking.SM_T_Report_Notes N
RIGHT OUTER JOIN
    (
    SELECT ReportGuid, Max(NoteDate) As NoteDate
    FROM Tracking.SM_T_Report_Notes
    GROUP BY ReportGuid
    ) AS ND
    ON  N.NoteDate = ND.NoteDate

score 11 · Accepted Answer · answered Jan 27 '11 at 13:28

11

You need to group by ReportGuid and select Max(NoteDate). That will select the maximum of each group.

answered Jan 27 '11 at 13:28

Adrian Grigore

33,034
36
130
210

That works if I only want ReportGuid and NoteDate but I need all of the columns of data. SELECT ReportGuid, Max(NoteDate) As NoteDate FROM T.SM_T_Report_Notes GROUP BY ReportGuid – Taryn Jan 27 '11 at 13:36
@bluefeet: Oh, ok. In that case you can simply SELECT max(ReportGuid), Max(NoteDate) As NoteDate FROM T.SM_T_Report_Notes GROUP BY ReportGuid – Adrian Grigore Jan 27 '11 at 13:52

SQL Select Max Date with Multiple records

1 Answers1

Linked