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 <[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
>
> 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_Effect


-- 
Dodger

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]