Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.
Course Of The Month
7 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
Active 3 days ago
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.
Active today
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
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special.
It's completely agentless, but does let you create an agent, if you desire.
It offers powerful scalability …
Suggested Courses
Course of the Month7 days, 6 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.