Tricky grouping query

[Joel Thornton]

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@tlchoice s.com>
(206) 709-2801 x24

[oj]

Re: Tricky grouping query

This should do...

select *
from Events e1
where EventId=(select top 1 EventId
from Events e2
where e2.PatientId=e1 .PatientId
order by e2.SeverityLeve l desc)

--
-oj

http://www.rac4sql.net

"Joel Thornton" <joelpt@eml.c c> wrote in message
news:c190a45a.0 401071336.8b7ee 44@posting.goog le.com...[color=blue]
> 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@tlchoice s.com>
> (206) 709-2801 x24[/color]

[David Portas]

Re: Tricky grouping query

SELECT S1.patientid, S1.eventid
FROM Sometable AS S1
JOIN
(SELECT patientid, MAX(severitylev el) AS severitylevel
FROM Sometable
GROUP BY patientid) AS S2
ON S1.patientid = S2.patientid
AND S1.severityleve l = S2.severityleve l

--
David Portas
------------
Please reply only to the newsgroup
--

[Joel Thornton]

Re: Tricky grouping query

Massively brilliant!

I wasn't aware of that subquery syntax you used there; I thought they
could only show up in the From clause or with the In (Select ...)
syntax.

Thanks very much oj.

Joel



"oj" <nospam_ojngo@h ome.com> wrote in message news:<Rp%Kb.770 145$Tr4.2209825 @attbi_s03>...[color=blue]
> This should do...
>
> select *
> from Events e1
> where EventId=(select top 1 EventId
> from Events e2
> where e2.PatientId=e1 .PatientId
> order by e2.SeverityLeve l desc)
>[/color]

[oj]

Re: Tricky grouping query

You're welcome. <G>

There's always more than one way to do things in sql. You would want to try
David's too and compare. Subquery is best if your outer query has lots of filter
(where clause) which results in a small resultset than it's quite *fast*.
However, if it returns a large resultset, the processing's required by the inner
subquery might be too high. This is because it's done for each row from the
outer query. So, the cost of generating a derived table (David's group by) might
be less and could outperform the my subquery.

--
-oj

http://www.rac4sql.net

"Joel Thornton" <joelpt@eml.c c> wrote in message
news:c190a45a.0 401072005.4523d 376@posting.goo gle.com...[color=blue]
> Massively brilliant!
>
> I wasn't aware of that subquery syntax you used there; I thought they
> could only show up in the From clause or with the In (Select ...)
> syntax.
>
> Thanks very much oj.
>
> Joel
>
>
>
> "oj" <nospam_ojngo@h ome.com> wrote in message[/color]
news:<Rp%Kb.770 145$Tr4.2209825 @attbi_s03>...[color=blue][color=green]
> > This should do...
> >
> > select *
> > from Events e1
> > where EventId=(select top 1 EventId
> > from Events e2
> > where e2.PatientId=e1 .PatientId
> > order by e2.SeverityLeve l desc)
> >[/color][/color]

[David Portas]

Re: Tricky grouping query

In addition to what OJ has said, notice the logical difference between our
two queries. OJ's returns one Event with the highest SeverityLevel for each
patient. My query returns all rows for the patient which have the highest
severity level. So for the following data:

CREATE TABLE Events (EventId INTEGER PRIMARY KEY, PatientId INTEGER NOT
NULL, SeverityLevel INTEGER NOT NULL)

INSERT INTO Events VALUES (1, 1, 5)
INSERT INTO Events VALUES (2, 1, 5)
INSERT INTO Events VALUES (3, 1, 1)

OJ's query will return one row, mine will return two rows.

A possible slight improvement to OJ's version in my opinion is to add
Eventid to the ORDER BY clause. This makes the logic of the query
consistent, otherwise if the maximum severity level is tied you can't
guarantee which row you will get back.

SELECT *
FROM Events E1
WHERE eventid=
(SELECT TOP 1 eventid
FROM Events E2
WHERE E2.patientid = E1.patientid
ORDER BY E2.severityleve l DESC, E2.eventid)

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

Re: Tricky grouping query

> A possible slight improvement to OJ's version in my opinion is to add[color=blue]
> Eventid to the ORDER BY clause. This makes the logic of the query
> consistent, otherwise if the maximum severity level is tied you can't
> guarantee which row you will get back.[/color]

Or, more sensibly, you might want to order by a date so that you get the
*latest*, most severe event for the patient. Whatever works best for you.

....
ORDER BY E2.severityleve l DESC, E2.eventdate DESC ??? )

--
David Portas
------------
Please reply only to the newsgroup
--

[oj]

Re: Tricky grouping query


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:ktKdnRudIL 0xY2Gi4p2dnA@gi ganews.com...
[color=blue]
> A possible slight improvement to OJ's version in my opinion is to add
> Eventid to the ORDER BY clause. This makes the logic of the query
> consistent, otherwise if the maximum severity level is tied you can't
> guarantee which row you will get back.[/color]

<G> Put a clustered index on EventID and we should get the desired row back
(even without the order by).
Yeah, it's better to be explicit so everyone is happy.

--
-oj

http://www.rac4sql.net