T-SQL: How to JOIN between tables and return the Max(date)

Hello,
I have 2 tables patients(patid,patname) ,  Rx (rx#,patid,drug,dispenseddt) which are joined on by the patientid

A patient  can have multiple medicines dispensed in the RX table.

How to find for each patient the latest dispensedate along with other fields patid and patname

Thanks.
Star79Asked:
Who is Participating?
 
Rajkumar GsSoftware EngineerCommented:
SELECT p.patid, p.patname, MAX(r.dispenseddt ) dispenseddt 
FROM patients p
INNER JOIN Rx r on p.patid = r.patid 
GROUP BY p.patid, p.patname

Open in new window


Also should work

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
SELECT p.*
FROM patients p
INNER JOIN
(
SELECT patid, MAX(dispenseddt) dispenseddt FROM Rx
GROUP BY patid
) r on p.patid = r.patid AND p.dispenseddt = r.dispenseddt
0
 
dannygonzalez09Commented:
something like this?

Select t1.patientid , Max(dispensedt) as DispensedDate
From table1 t1
join table2 t2
On t1.patientid = t2.patientid
Group By t1.PatientId
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
awking00Commented:
select patid, patname, rx#, drug, dispenseddt from
(select r.patid, p.patname, r.rx#, r.drug, r.dispenseddt,
 row_number() over (partition by r.patid order by r.dispenseddt desc) rn
 from patients p, rx r
 where p.patid = r.patid) as x
where x.rn = 1;
0
 
awking00Commented:
Perhaps I misunderstood the question, but that solution won't provide the prescription number and drug values which I thought you also wanted.
0
 
Star79Author Commented:
awking00,
I only wanted the patid and patname along with the dispensdt
 to be retrieved
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.

All Courses

From novice to tech pro — start learning today.