MYSQL query

--_c9505db8-a972-4915-b6c9-9ec24e04e484_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


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 d=
iag_icd9 like "556%")
group by visit_time;

and the result was..

pat_id visit_time =20
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=
=3D '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 c=
orrectly.

Any suggestions would be of great help

thank you
Kiran



____________________________________________________________ _____
i=92m is proud to present Cause Effect, a series about real people making a=
difference.
http://im.live.com/Messenger/IM/MTV/?source=3Dtext_Cause_Eff ect=

--_c9505db8-a972-4915-b6c9-9ec24e04e484_--

RE: MYSQL query

--_92f0d689-b0e1-410d-a8db-62947d6cbf4b_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

More questions about the query...

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

Relationships=20
Patient and Visit Table ----> Each pat_id can have multiple visits with a u=
nique 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%) ba=
sed 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=20
join cag_pp2test.diagnosis D on D.visit_id=3DV.visit_id=20
join cag_pp2test.patient P on P.pat_id=3DV.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: wdhawes@gmail.com
> To: anna3144@hotmail.com
> Subject: Re: MYSQL query
>=20
> On 28/12/2007, Kiran Annaiah 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 vi=
sit_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 pa=
t_id =3D '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 o=
ut correctly.
> >
> > Any suggestions would be of great help
>=20
> 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.
>=20
> SELECT pat_id, max(visit_time) ... FROM ... GROUP BY pat_id
>=20
> 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=3DTXT_TAGHM_Wave2 _sharelife_1220=
07=

--_92f0d689-b0e1-410d-a8db-62947d6cbf4b_--

Re: MYSQL query

Kiran Annaiah wrote:
> group by visit_time;
>
That means 'create one result row for each unique value of visit_time' -
perhaps you wanted to group by pat_id, so you can see the
max(visit_time) for each unique value of pat_id?

cheers
John


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: MYSQL query

SELECT 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 pat_id

Which would translate to: "Give me a row for each pat_id where the
visit_id is in this subquery, showing the MAX value of each" is what
the above translates to.

Your original query translates to:
"Give me a row for each max value of the first value found for pat_id
where the visit_id is in this subquery, showing the pat_id for each"

Distinct isn't necessary, as grouping by a column gives you only one
of each column. Moreover, distinct will mess up the results because it
will only examine the first row found of each value for that column.
Therefore the MAX value will always be the same as the value of the
first one examined, because it's only omparing one thing to itself to
determine MAX-ness.
However, grouping by the thing you want one-of-each for IS necessary,
else you'll get one value for each MAX value, rather than one value
for each pat_id.

On 28/12/2007, Kiran Annaiah 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
>
> thank you
> Kiran
>
>
>
> ____________________________________________________________ _____
> i'm is proud to present Cause Effect, a series about real people making a difference.
> http://im.live.com/Messenger/IM/MTV/?source=text_Cause_Effec t


--
Dodger

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org