troubleshooting Question

SQL Query - Select Top or Max

Avatar of Jeff S
Jeff SFlag for United States of America asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
6 Comments2 Solutions421 ViewsLast Modified:
Per my client, they need the last HGBA1C Value (which is the o.OBSVALUE) along with the last date that corresponds with this (o.ObsDate). In my query below, I am getting more then one record for a specific patient. How could I just return the patients one date and value versus them all? SELECT TOP 1 maybe? Not 100% sure.

---- Last HGBA1C Value / Date Last HGBA1C Value / (HGBA1C is obs.HDID = 28)

SELECT  o.pid,
        pp.PatientId,
        pp.PatientProfileId,
        dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
        MAX(o.OBSDate) AS obsdate,
        o.OBSVALUE
FROM    OBS AS o
        JOIN OBSHEAD AS oh ON o.HDID = oh.HDID
        JOIN PatientProfile AS pp ON o.PID = pp.PId
WHERE   o.HDID = 28
GROUP BY o.PID,
        pp.PatientId,
        pp.PatientProfileId,
        dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix),
        o.OBSVALUE
ASKER CERTIFIED SOLUTION
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 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 2 Answers and 6 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