Tricky Grouping Query

I'm having much difficulty figuring out how to write the following
query. Please help!

I have this table:

Event
EventId int Primary Key
PatientId int
SeverityLevel int

What I want returned in my query is a list of all (distinct)
PatientIds appearing in Event, with the *most severe* EventId returned
for each Patient. The higher the value of SeverityLevel, the more
severe that Event is considered to be.

The problem I am having is that I can't figure out how to (a) group by
PatientId, AND (b) return the EventId of the highest-severity Event
for *each* PatientId (Order By SeverityLevel Desc).


So if my table contained:

EventId PatientId SeverityLevel
------- --------- -------------
1 1 0
2 1 1
3 1 5
4 2 5
5 2 2

I would want my result set to be:

PatientId EventId
--------- -------
1 3
2 4

since events 3 and 4 are the most severe events for patients 1 and 2,
respectively.

Any help would be greatly appreciated. This seems to be something that
could be handled easily with a FIRST() aggregate operator (as in MS
Access) but this is apparently lacking in SQL Server. Also note there
may be multiple Events with a given PatientId and SeverityLevel, in
that case I'd want only one of the EventIds (the Max() one).


Many thanks,

Joel Thornton
Developer, Total Living Choices
<joelt@tlchoices.com>
(206) 709-2801 x24