SQL Query - Select Top or Max

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

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
try this.
SELECT * 
  FROM (SELECT *, 
               ROW_NUMBER() 
                 OVER(PARTITION BY PatientId ORDER BY obsdate DESC) rn 
          FROM (  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) t1) t2 
 WHERE rn = 1

Open in new window

0
 
Alex MatzingerDatabase AdministratorCommented:
Use

Select Top 1 [columns you want]
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you might want to read this article:
http://www.experts-exchange.com/A_3203.html
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Ephraim WangoyaCommented:
SELECT  top 1 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
ORDER BY o.OBSVALUE DESC
0
 
Jeff SAuthor Commented:
ewangoya -

I started out with 28 records in my data set with multiple patients (some having more then one OBSVALUE. When I use your example, I just get the most recent overall. I want the most recent per distinct patient. I am reading through Angels post and think this may be more in line with what I need, just need to read through it all.
0
 
Jeff SAuthor Commented:
Thanks!!
0
All Courses

From novice to tech pro — start learning today.