troubleshooting Question

Max function not returning last visit

Avatar of IntercareSupport
IntercareSupport asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
10 Comments1 Solution335 ViewsLast Modified:
I want to only display a patient's record for their last visit.

When I execute this code:
SELECT     mpl_id, Last_Name, First_Name, HP_Last_Visit
FROM         Master_Patient_List m
WHERE HP_Last_Visit = (SELECT MAX(HP_Last_Visit)  FROM Master_Patient_List l  WHERE l.mpl_id = m.mpl_id )

I get these results (for one patient):

mpl_id      Last_Name      First_Name      HP_Last_Visit
3195967      LNAME      FNAME      2006-11-27
3238261      LNAME      FNAME      2005-07-13
3238262      LNAME      FNAME      2006-03-10

I need to only have the last visit displayed (in this case it's 3195967 record).  Thanks!
ASKER CERTIFIED SOLUTION
Aneesh
Database Consultant
Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros