(MAX) Group By query (1 Viewer)

jsdba · Jun 9, 2019

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.

Code:

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 · Jun 9, 2019

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

Code:

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 · Jun 9, 2019

Or how about?

Code:

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 · Jun 9, 2019

theDBguy said:

Or how about?

Code:

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 · Jun 9, 2019

jsdba said:
Worked like a charm. Thank you

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

(MAX) Group By query (1 Viewer)

jsdba

Registered User.

isladogs

MVP / VIP

theDBguy

I’m here to help

jsdba

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread