Need Latest Event Of Certain Type Where Several Events May Exist For That Date

Question

First of all, I'm working in SQL Server 2000 (although prayers have been made to the Federal Stimulus Money gods for an upgrade to 2008.)

I have a listing of students and educational events. I can successfully get the most recent event, except that it may be a combination of events:

StudentID    Event      Date  
1            Triennial  7/1/2009  
1            Annual     7/1/2009  
2            Annual     3/3/2009  
3            Annual     6/23/2009  
3            Triennial  6/23/2009  
4            Annual     2/1/2009  
4            Triennial  2/1/2009  
5            Annual     10/1/2009

One event date may have two events, "Annual" and "Triennial," or it may just have one event, "Annual." If it's a combination event, I only want to select "Triennial." If it's just an Annual event, I'll just select "Annual."

Basically, every event has "Annual," but some have "Triennial" as well, and if it's a Triennial I need to select that Event instead of the description "Annual." So for the data above, I'd want results like this:

StudentID    Event      Date  
1            Triennial  7/1/2009  
2            Annual     3/3/2009  
3            Triennial  6/23/2009  
4            Triennial  2/1/2009  
5            Annual     10/1/2009

Answer 1

SELECT StudentID, [Date], MAX(Event) AS [Event]
FROM   MyTable
GROUP BY StudentID, [Date]

Should do it.....(Triennial is "greater than" Annual)