Solved

SQL Query - Select Top or Max

Posted on 2011-03-07
6
383 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
6 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35060962
Use

Select Top 1 [columns you want]
0
 
LVL 142

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:ewangoya
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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 40

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now