asked on 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
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
Last Comment
Jeff S
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks!!
Select Top 1 [columns you want]