Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2013-11-05
Medium Priority
Last Modified: 2013-11-06
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

Question by:Star79
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39624577
FROM patients p
SELECT patid, MAX(dispenseddt) dispenseddt FROM Rx
GROUP BY patid
) r on p.patid = r.patid AND p.dispenseddt = r.dispenseddt
LVL 23

Accepted Solution

Rajkumar Gs earned 2000 total points
ID: 39624595
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


Expert Comment

ID: 39624596
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 32

Expert Comment

ID: 39624868
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;
LVL 32

Expert Comment

ID: 39625279
Perhaps I misunderstood the question, but that solution won't provide the prescription number and drug values which I thought you also wanted.

Author Comment

ID: 39627648
I only wanted the patid and patname along with the dispensdt
 to be retrieved

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question