(MAX) Group By query

[jsdba]

Hi Experts

I have a table tblNote with the following fields:

• NoteID
• TaskID
• ReminderDate
• Note

I need a select query that will retrieve the record with furthest date group by TaskID. The query must include the the Note field. Heres my starting point.

SELECT TaskID, MAX(ReminderDate), Note FROM tblNote Group By TaskID

This doesn't work because i know i also need to group by Note. Can anyone point me in the right direction.

 

[isladogs]

i'm not clear what you're asking but possibly just

SELECT TaskID, Note, MAX(ReminderDate) FROM tblNote Group By TaskID, Note

If that's not right can you explain what you mean by furthest date group.
Preferably with some example data for clarity.

 

[theDBguy]

Or how about?

SELECT T1.TaskID, T1.ReminderDate, T1.Note
FROM tblNote T1
INNER JOIN (SELECT T2.TaskID, MAX(T2.ReminderDate) As LastNoteDate
     FROM tblNote T2
     GROUP BY T2.TaskID) SQ
ON T1.TaskID=SQ.TaskID
     AND T1.RemiderDate=SQ.LastDate

 

[jsdba]

Or how about?

SELECT T1.TaskID, T1.ReminderDate, T1.Note
FROM tblNote T1
INNER JOIN (SELECT T2.TaskID, MAX(T2.ReminderDate) As LastNoteDate
     FROM tblNote T2
     GROUP BY T2.TaskID) SQ
ON T1.TaskID=SQ.TaskID
     AND T1.RemiderDate=SQ.LastDate

Worked like a charm. Thank you

 

[theDBguy]

Worked like a charm. Thank you

Hi. You’re welcome. Colin and I were happy to assist. Good luck with your project.