Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 809
  • Last Modified:

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!
 
0
garus
Asked:
garus
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:

SELECT *
FROm urTable u
WHERE med_Date = (SELECT  MIN(Med_Date) from urTable t where t.Pt_num = u.pt_num )
0
 
Patrick MatthewsCommented:
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
0
 
Anthony PerkinsCommented:
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
0
 
Patrick MatthewsCommented:
acperkins said:
>>I beleive you also have to iinclude the date in the self-join.

Quite right.  All three columns are needed :)
0
 
garusAuthor Commented:
Yes, just figured that out. I was getting tons of rows and then I realized there was no date.
Thank you guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now