maxdate not bringing back single line I am using SQL Server Management Studio.I have tried several max date options to return the latest records and notes but keep getting multiple entries instead of just the latest one.  can you advise where I have gone wrong and suggest better alternative

SELECT     [Pupil ID], [Event Date],[Header Note], [Module ID], [Event ID] AS ID
FROM         dbo.vwPupil_EventType EV
WHERE     ([Module ID] = 22)
GROUP BY [Event ID],[Pupil ID], [Event Date],[Header Note],[Module ID]  
HAVING [Event Date]IN (SELECT MAX([Event Date])
FROM dbo.vwPupil_EventType
WHERE EV.[Event ID] = [Event ID])
ORDER BY [Pupil ID]



  This gives the error... Msg 306, Level 16, State 2, Line 4


The text, ntext, and image data types can not be compared or sorted, except when using 'IS NULL' or 'LIKE' operator.

So proceeded onto this 2nd attempt and used CAST, this got rid of the 306 error but is still not returning the maximum entry, ie for Pupil id 59775, only 1 line should be returned showing the entry for 2008-01-08

SELECT     [Pupil ID], [Event Date], CAST([Header Note]AS VARCHAR(100))NOTE, [Module ID], [Event ID] AS ID
FROM         dbo.vwPupil_EventType EV
WHERE     ([Module ID] = 22)
GROUP BY [Event ID],[Pupil ID], [Event Date],CAST([Header Note]AS VARCHAR(100)),[Module ID]  
HAVING [Event Date]IN (SELECT MAX([Event Date])
FROM dbo.vwPupil_EventType
WHERE EV.[Event ID] = [Event ID])
ORDER BY [Pupil ID]


also tried

SELECT     [Pupil ID], MAX(CAST([Event Date] AS DATETIME)), CAST([Header Note]AS VARCHAR(100))NOTE, [Module ID], [Event ID] AS ID
FROM         dbo.vwPupil_EventType EV
WHERE     ([Module ID] = 22)
GROUP BY [Event ID],[Pupil ID], [Event Date],CAST([Header Note]AS VARCHAR(100)),[Module ID]  
HAVING [Event Date]IN (SELECT MAX([Event Date])
FROM dbo.vwPupil_EventType
WHERE EV.[Event ID] = [Event ID])
ORDER BY [Pupil ID]
You didn't supply any test data to validate my results against and perhaps I'm misunderstanding exactly what you are trying to achieve, but it seems this would give you the correct answer.

Declare @T table 
(
[pupil id] int,
[event date] date,
[header note] varchar(50),
[module id] int,
[event id] int
)

insert into @T values(1,'2010-01-01','note1',22,1)
insert into @T values(1,'2016-01-01','latest note',22,1)

insert into @T values(2,'2013-01-01','note1',22,1)
insert into @T values(2,'2015-01-01','latest note',22,1)

SELECT [Pupil ID]
    ,[Event Date]
    ,[Header Note]
    ,[Module ID]
    ,[Event ID] AS ID
FROM @T EV
WHERE [Module ID] = 22
    AND [Event Date] = (
        SELECT MAX([Event Date])
        FROM @t
        WHERE [Pupil ID] = EV.[Pupil ID]
            AND [Module ID] = EV.[Module ID]
        )