TSQL各种最大日期选项不起作用

I am trying to get the Maximum date and details on my report but they still return all entries within a given time span instead of just the latest entry details.

我试图获取我的报告的最大日期和详细信息，但他们仍然返回给定时间范围内的所有条目，而不仅仅是最新的条目详细信息。

the table name is dbo.vwPupil_EventType:

表名是dbo.vwPupil_EventType：

EventID (int,not null)
PupilID (int,not null)
EventDate (datetime,null)
Header Note(text,null)
Event Type (varchar(100),null)
Module ID (int,null)

1st attempt generated error msg 306:

第一次尝试生成错误消息306：

the text, ntext and image data types cannot be compared

text，ntext和image数据类型无法比较

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]

Tried these other 2 options but still getting all entries within a date span instead of just the latest one:

尝试了其他两个选项，但仍然获得日期范围内的所有条目，而不仅仅是最新的条目：

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]

then 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]

2 个解决方案

#1

This is typically solved using a window function:

这通常使用窗口函数来解决：

select [Pupil ID], [Event Date],[Header Note], [Module ID], [ID]
from (
  SELECT   [Pupil ID], [Event Date],[Header Note], [Module ID], [Event ID] AS ID, 
           row_number() over (partition by [Pupil ID] order by [Event Date] desc) as rn
  FROM     dbo.vwPupil_EventType EV
  WHERE    [Module ID] = 22
) t
where rn = 1;

If there are multiple rows with the same "max date", you'll only get one of them. If you want all of them returned use dense_rank() instead of row_number()

如果有多个行具有相同的“最大日期”，则您只能获得其中一个。如果你想让它们全部返回使用dense_rank（）而不是row_number（）

#2

If I understood you requirement correctly they this may help you

如果我理解你的要求，他们可以帮助你

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
         AND CAST([Event Date] AS DATETIME) = (SELECT MAX(CAST([Event Date] AS DATETIME))
                                                FROM dbo.vwPupil_EventType)
ORDER BY [Pupil ID]

#1