Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.
Solved
SQL Query - Select Top or Max
Posted on 2011-03-07
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
[X]
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
-
2
- +2
6 Comments
you might want to read this article:
http://www.experts-exchange.com/A_3203.html
http://www.experts-exchange.com/A_3203.html
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
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.
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
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs).
Being someone who is always looking for alternatives to "work your data", I came across this …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash"
After Shake : http://www.videocopilot.net/presets/after_shake/
All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month12 days, 6 hours left to enroll
650 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.