• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

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.
0
Star79
Asked:
Star79
1 Solution
 
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
 
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
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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