asked on

ms sql server 2005 get min date per patient, medication_type

Hi,
I have a table with the following information:

pt_num, med_code, med_name, med_type, med_date
1 123 abc a 2000/01/17
1 148 bgh a 2001/02/09
1 368 ghi b 2001/03/09
...

and I need to get something like
pt_num med_code , med_name, Med_type, med_date
1 123 abc a 2000/01/17
1 368 ghi b 2001/03/09

basically, the first patient-medication of each medication_type

any suggestions? I've tried some things, but I get stuck when I group by med_code, or if I get all first medications for each patient by med code and then I try to group by med_type keeping in the med_code and all the other stuff.

Any suggestions?
Thanks!

Aneesh

SELECT *
FROm urTable u
WHERE med_Date = (SELECT MIN(Med_Date) from urTable t where t.Pt_num = u.pt_num )

Patrick Matthews

Have you tried:

SELECT t1.pt_num, t1.med_code, t1.med_name, t1.med_type, t1.med_date
FROM SomeTable t1 INNER JOIN
      (SELECT t2.pt_num, t2.med_type, MIN(med_date) AS med_date
      FROM SomeTable t2
      GROUP BY t2.pt_num, t2.med_type) q ON t1.pt_num = q.pt_num AND t1.med_type = q.med_type
ORDER BY t1.pt_num, t1.med_type

Anthony Perkins

I beleive you also have to iinclude the date in the self-join. So to plagiarize matthewspatrick solution (no points please):
SELECT t1.pt_num,
t1.med_code,
t1.med_name,
t1.med_type,
t1.med_date
FROM SomeTable t1
INNER JOIN (
                  SELECT      t2.pt_num,
                              t2.med_type,
                              MIN(med_date) AS med_date
                  FROM      SomeTable t2
                  GROUP BY
                              t2.pt_num,
                              t2.med_type
) q ON t1.pt_num = q.pt_num
                                          AND t1.med_type = q.med_type
                                          AND t1.med_date = q.med_date
ORDER BY
            t1.pt_num,
t1.med_type

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garus

ASKER

Yes, just figured that out. I was getting tons of rows and then I realized there was no date.
Thank you guys!