[AccessD] group min - max #2

Pedro,

I redid the query in original response  as a single query

SELECT pt.patient_number
     , pt.PDate
     ,pt.AdditionalFld
  FROM ( SELECT Patient_Number
              , Min(AdditionalFld) AS min_AF
           FROM PedroTbl
         GROUP
             BY Patient_Number ) AS m
INNER
  JOIN PedroTbl AS pt
    ON (
      pt.Patient_number = m.Patient_Number
   AND pt.AdditionalFld = m.min_AF
       )

jack

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pedro at plex.nl
Sent: Monday, December 06, 2010 1:36 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] group min - max

Dear list,

normally i use Group by and min max, to create a list of min or max (or
first-last) dates per Patient. 

But when there is a third column which has different values, I'll make a key
from date and patient-number and then ad the extra value.
How can i do this in one simple query?

I have

001    1-1-10    5
001    2-2-10    7
002    3-2-10    8
002    3-2-10    6

i need 

001    1-1-10    5
002    3-2-10    6

Thanks

Pedro
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com