Jump to content

Tricky SQL query


mastubbs

Recommended Posts

OK time for a very tricky mysql query (well, tricky for me anyway)…

 

I have two tables: ‘patients’ and ‘obs’

 

Variables in patients: MRN, name, others1

 

Variables in obs: MRN, par (numeric value), time_recorded (datetime value), others2

 

Each time a new par value is added to obs: MRN, datetime, others 2 are recorded also (there are no null values). Multiple par values are therefore added over time for the same MRN number (as different rows in the table)

 

I want to make a query that will select * from patients and * from obs using inner join using MRN only when the most recent par value for each unique MRN in obs was >6

 

For some context: MRNs are hospital numbers of patients and par is a score of how unwell they are. I am trying to produce a list of all the patients who’s most recent par value recorded in obs is >6 and print their details.

 

I think I have managed to work out the inner join part:

SELECT * from obs INNER JOIN patients ON obs.MRN = patient.MRN.

What I just can’t figure out however is how to select only records where the most recent par for that particular MRN (ie patient) was > 6.

 

I suppose one way to do this is to select all data from both tables using inner join MRN where obs.par >6, and then sort by mrn, the sort by datetime decending, and then only print the top returned result for each unique mrn, but im not really sure if that last part is possible?

 

I hope all that makes sense? Any guidance or ideas anyone has about how to achieve this would be very helpful. 

 

Thanks all in advance for any replies,

 

Matt

Link to comment
Share on other sites

I want to make a query that will select * from patients and * from obs using inner join using MRN only when the most recent par value for each unique MRN in obs was >6

(emphasis added)

 

SELECT * 
FROM obs INNER JOIN patients ON obs.MRN = patients.MRN
WHERE obs.time_recorded = (SELECT MAX(OLAST.time_recorded) FROM obs AS OLAST WHERE OLAST.MRN = patients.MRN)
AND obs.par > 6
Link to comment
Share on other sites

mastubbs, on 03 Jun 2013 - 3:15 PM, said:snapback.png

I want to make a query that will select * from patients and * from obs using inner join using MRN only when the most recent par value for each unique MRN in obs was >6

 

This should fulfill all the requirements:

Test data:

Patients:

mrn  name      others1

1     'name1'   'xxx'

2     'name2'   'yyy'

 

Obs:

mrn  par time_recorded             others2

1       1   2013-06-03 16:29:35  'a'
1       2   2013-06-03 16:29:51  'b'
1       6   2013-06-03 16:30:05  'c'
1       7   2013-06-03 16:30:18  'd'
2       8   2013-06-03 16:32:30  'e'
2       2    2013-06-03 16:32:42  'f'
2       7    2013-06-03 16:32:51  'g'
2       5    2013-06-03 16:33:03  'h'

 

SELECT patients.*, obs.*
  FROM patients
  JOIN obs ON patients.mrn = obs.mrn
  JOIN (  SELECT mrn, MAX(time_recorded) AS maxdate
            FROM obs
            WHERE par > 6
            GROUP BY mrn
       ) AS b ON obs.mrn = b.mrn AND obs.time_recorded = b.maxdate;
Edited by mikosiko
Link to comment
Share on other sites

Thanks all for the help,

 

 

(emphasis added)
 

SELECT * 
FROM obs INNER JOIN patients ON obs.MRN = patients.MRN
WHERE obs.time_recorded = (SELECT MAX(OLAST.time_recorded) FROM obs AS OLAST WHERE OLAST.MRN = patients.MRN)
AND obs.par > 6

DavidAM's worked the best, thanks for that.

 

 

 

SELECT patients.*, obs.*
  FROM patients
  JOIN obs ON patients.mrn = obs.mrn
  JOIN (  SELECT mrn, MAX(time_recorded) AS maxdate
            FROM obs
            WHERE par > 6
            GROUP BY mrn
       ) AS b ON obs.mrn = b.mrn AND obs.time_recorded = b.maxdate;

 

Mikosiko's idea did almost work, but in fact only showed the last par > 6 for a unique mrn, rather than if the last par score was >6, if that makes sense. so any record with a par>6 showed up even if that was not the most recent par score for that mrn.

 

Thanks again,

 

Matt

Link to comment
Share on other sites

just to satisfy my curiosity... given the example data that I did provide before:

Patients:

mrn  name      others1

1     'name1'   'xxx'

2     'name2'   'yyy'

 

Obs:

mrn  par time_recorded             others2

1       1   2013-06-03 16:29:35  'a'
1       2   2013-06-03 16:29:51  'b'
1       6   2013-06-03 16:30:05  'c'
1       7   2013-06-03 16:30:18  'd'
2       8   2013-06-03 16:32:30  'e'
2       2    2013-06-03 16:32:42  'f'
2       7    2013-06-03 16:32:51  'g'
2       5    2013-06-03 16:33:03  'h'
 
could you show us which was the expected results?... asking because your last sentence "so any record with a par>6 showed up even if that was not the most recent par score for that mrn." is kind of confusing and not coincident with the results that I got..
 
the results that I got from the proposed query are:
1, 'name1', 'xxx',   1, 7, 2013-06-03 16:30:18, ''
2, 'name2', 'yyy',  2, 7, 2013-06-03 16:32:51, ''
 
and they are not showing every record with par > 6 ...look the results for the mrn=2... it is showing exactly 1 record which is the most recent par score > 6 isn't?
 
maybe I misunderstood your objectives?
Link to comment
Share on other sites

it is showing exactly 1 record which is the most recent par score > 6 isn't?

He's not trying to find "the most recent par score > 6", he's trying to find entries "who's most recent entry has par > 6". Entry 2 should not qualify because their most recent par score is 5.

Edited by kicken
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.