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 7 2497
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
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
--
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)
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)
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
--
> 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
--
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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?
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
|
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...
| |