Tricky SQL query

[mastubbs]

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

[Jessica]

WHERE par > 6

[The Letter E]

your theory seems logical mastubbs. order by date desc and don't forget a limit 1 in there, if you only want the most recent.

Edited June 4, 2013 by The Letter E

[DavidAM]

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

[Jessica]

My bad

[mikosiko]

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

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 June 4, 2013 by mikosiko

[mastubbs]

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

[mikosiko]

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?

[kicken]

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 June 4, 2013 by kicken

[mikosiko]

Thanks for the clarification kitchen... clearly I misunderstood the objective .

[mastubbs]

Hi Both,

 

Thanks for that kitchen, i couldn't have explained it better myself (clearly, lol). Thanks very much to both of you for your help with this.

 

Matt

[Jessica]

Thanks for that kitchen

 

@kicken:

[mastubbs]

lol sorry kicken, slip of the finger.