473,325 Members | 2,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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
<jo***@tlchoices.com>
(206) 709-2801 x24
Jul 20 '05 #1
7 2497
oj
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
"Joel Thornton" <jo****@eml.cc> wrote in message
news:c1*************************@posting.google.co m...
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
<jo***@tlchoices.com>
(206) 709-2801 x24

Jul 20 '05 #2
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
--
Jul 20 '05 #3
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" <no**********@home.com> wrote in message news:<Rp%Kb.770145$Tr4.2209825@attbi_s03>...
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)

Jul 20 '05 #4
oj
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" <jo****@eml.cc> wrote in message
news:c1**************************@posting.google.c om...
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" <no**********@home.com> wrote in message

news:<Rp%Kb.770145$Tr4.2209825@attbi_s03>...
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)

Jul 20 '05 #5
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
--
Jul 20 '05 #6
> 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.


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
--
Jul 20 '05 #7
oj

"David Portas" <RE****************************@acm.org> wrote in message
news:kt********************@giganews.com...
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.


<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
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Debbie Davis | last post by:
Hi there, SQL 2000 I have the following query: SELECT sponsor, COUNT(sponsor) * 2 AS total FROM Referrals GROUP BY sponsor Works great, returns the sponsor and the total * 2 of their...
1
by: Brian Coy | last post by:
I am creating a database to track scrap on a daily basis at my plant. I have to provide a weekly scrap report with the amount of each part scrapped per day. I have the basic database set up, and...
3
by: ahaque38 | last post by:
Hello. Using A2K SP3, I am having the following problem with a report using "Sorting and Grouping". I have recently added a grouping in the reports for "Category2<>'CONTRACTS'". I have...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
1
by: Regnab | last post by:
I often want to query the database with certain parameters in my query which I do not want included in the query result. The problem is when I want to group on 1, sum the 2nd (and group by) and...
3
by: Dave | last post by:
So I'm trying to write a CSS preprocessor. I want to add the ability to append a selector onto other selectors. So, given the following code: ========================================= #selector...
3
by: UHelix | last post by:
Hi, I'm not really even sure how to phrase this question, so here is a sample of exactly what I want to do. In the following data set I would like to select the entry from the group of samples 1...
6
by: craig.buchinski | last post by:
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i have written a query for a report to pull out our streetlights and group them by billing wattage. The problem is that it is...
3
by: Jimmy | last post by:
Is there a way to sort/group a report based on the second column of a combo box, i.e. the text associated with the primary key number?
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.