Tricky grouping query -- sql server (alternate)

joelpt NO[at]SPAM eml.cc
1/7/2004 1:36:44 PM

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>

joelpt NO[at]SPAM eml.cc
1/7/2004 8:05:30 PM

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

[quoted text, click to view]

oj
1/7/2004 10:06:41 PM

This should do...

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

--
-oj
http://www.rac4sql.net

[quoted text, click to view]

David Portas
1/7/2004 10:40:02 PM

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

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

oj
1/8/2004 6:13:53 AM

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

[quoted text, click to view]

David Portas
1/8/2004 6:57:17 AM

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.severitylevel DESC, E2.eventid)

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

David Portas
1/8/2004 7:00:52 AM

[quoted text, click to view]

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.severitylevel DESC, E2.eventdate DESC ??? )

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

oj
1/8/2004 7:43:50 AM

[quoted text, click to view]

<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

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Tricky grouping query