Solved

SQL Query - Select Top or Max

Posted on 2011-03-07
6
388 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Jeff S
[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
6 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35060962
Use

Select Top 1 [columns you want]
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 35060975
you might want to read this article:
http://www.experts-exchange.com/A_3203.html
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35061070
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 7

Author Comment

by:Jeff S
ID: 35061154
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
 
LVL 41

Accepted Solution

by:
Sharath earned 400 total points
ID: 35063266
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
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 35063355
Thanks!!
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question