RE: MYSQL query

More questions about the query...

i have 3 tables that i am tryin to pull information from -

Relationships 
Patient and Visit Table ----> Each pat_id can have multiple visits with a unique visit_id for each visit (and visit_time is recorded)
Diagnosis and Visit ----> every visit_id has a diag_icd9 associated with it

Diagnosis
-----------
diag_name
diag_icd9
visit_id


Patient
--------
pat_id
pat_gender

Visit
------
visit_id
pat_id
visit_time
visit_reason


I am trying to get the all pat_ids where the diag_icd9 is (555% or 556%) based on the most recent visit

this is the query i have so far (which almost works but some unwanted icd9 codes slip thru, maybe cos of the join i am doing after the subquery)

select distinct V.pat_id, P.pat_gender, V.visit_id, max(visit_time), D.diag_name, D.diag_icd9 from cag_pp2test.visit V 
join cag_pp2test.diagnosis D on D.visit_id=V.visit_id 
join cag_pp2test.patient P on P.pat_id=V.pat_id
where V.visit_id in (
select visit_id from cag_pp2test.diagnosis D where D.diag_icd9 like "555%" or diag_icd9 like "556%")
group by pat_id
order by pat_id asc;


Results i get
---------------
pat_id           pat_gender-- visit_id --visit_time-- diag_name--diag_icd9
-----------------------------------------------------------------------------------
1007727213    F    197317    15:26:00    ULCERATIVE COLITIS    556.9
1011440034    M    405037    15:00:00    RECTAL   ANAL HEMORRHAGE    569.3
1032366203    F    13440    18:29:00    ATTN DEFIC NONHYPERACT    314.00
1227859831    M    168348    21:30:00    REGIONAL ENTERITIS NOS    555.9
1259865856    F    53986    10:35:00    REGIONAL ENTERITIS NOS    555.9
1283239125    M    157134    13:42:00    FUNCTION DIS NEUTROPHILS    288.1


How can i get those unwanted icd9 from showing up?

thank you
Kiran


> Date: Fri, 28 Dec 2007 17:04:49 +0000
> From: [email protected]
> To: [email protected]
> Subject: Re: MYSQL query
> 
> On 28/12/2007, Kiran Annaiah <[email protected]> wrote:
> >
> > Hi I am tryin out a query to get the newest entry of a particular value.
> >
> > Query1
> >
> > select distinct pat_id, max(visit_time) from cag_pp2test.visit where visit_id in (
> > select visit_id from cag_pp2test.diagnosis where diag_icd9 like "555%" or diag_icd9 like "556%")
> > group by visit_time;
> >
> > and the result was..
> >
> > pat_id              visit_time
> > 593810537    00:15:00
> > 8174391599    00:26:00
> > 1599174133    00:27:00
> > 9439759022    00:29:00
> > 7065947263    00:32:00
> > 5850634830    00:36:00
> >
> >
> > Crosscheck query
> > Query 2
> >
> > select distinct pat_id, max(visit_time) from cag_pp2test.visit where pat_id = '593810537'
> > group by visit_time;
> >
> > and result was
> >
> > pat_id           visit_time
> > 593810537    00:15:00 (first result row)
> > 593810537    03:33:00
> > 593810537    07:49:00
> > 593810537    08:01:00
> > 593810537    08:31:00
> > ...
> > ...
> > ...
> > 593810537    21:03:00
> > 593810537    21:08:00
> > 593810537    21:10:00 (last result row)
> >
> >
> > and i feel it is just returning the first value from visit_time column. How does it sort the max time values?
> > Was a little confused, was wondering if my query was pulling the time out correctly.
> >
> > Any suggestions would be of great help
> 
> If I understand you correctly, each pat_id can have multiple
> visit_time values and you want to retrieve each unique pat_id with its
> most recent visit_time. If that's the case, you should not be grouping
> on the visit_time, only on pat_id.
> 
> SELECT pat_id, max(visit_time) ... FROM ... GROUP BY pat_id
> 
> You don't generally group by a field when you want to obtain aggregate
> values from it using MAX(), MIN(), AVG(), etc.

_________________________________________________________________
Share life as it happens with the new Windows Live.
http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_122007