CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
Daniel
Daniel

Reputation: 173

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

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

Upvotes: 0

Views: 65

Answers (1)

Aaron Alton
Aaron Alton

Reputation: 23226

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

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

Upvotes: 1

Related Questions