locked
SELECT TOP 1 help needed RRS feed

  • Question

  • 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? I tried select TOP 1 and it only gave me the last record in the obs. I need the value for each distinct patient not just the last one in the obs table.

    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
        AND ( o.State IS NULL
           OR o.STATE NOT IN ( 'D', 'R', 'I', 'P', 'S' )
          )
    GROUP BY 
    		o.PID,
        pp.PatientId,
        pp.PatientProfileId,
        dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix),
        o.OBSVALUE
    
    Monday, March 7, 2011 9:03 PM

Answers

  • There are many different solutions available for this problem. One of them which is SQL Server 2005+ solution is

    ;with cte as (SELECT o.pid,
    
     pp.PatientId,
    
     pp.PatientProfileId,
    
     dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
    
     o.OBSdate,
    
     o.OBSVALUE,
     row_number() over (partition by pp.PatientID order by o.OBSdate DESC) as row
    
    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
    
     AND ( o.State IS NULL
    
      OR o.STATE NOT IN ( 'D', 'R', 'I', 'P', 'S' )
    
      ))
    
    select * from cte where Row = 1
    
    
    
    
    
    You can check other solutions at this blog post http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/including-an-aggregated-column-s-related
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by JeffS2002 Monday, March 7, 2011 9:31 PM
    Monday, March 7, 2011 9:10 PM

All replies

  • There are many different solutions available for this problem. One of them which is SQL Server 2005+ solution is

    ;with cte as (SELECT o.pid,
    
     pp.PatientId,
    
     pp.PatientProfileId,
    
     dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
    
     o.OBSdate,
    
     o.OBSVALUE,
     row_number() over (partition by pp.PatientID order by o.OBSdate DESC) as row
    
    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
    
     AND ( o.State IS NULL
    
      OR o.STATE NOT IN ( 'D', 'R', 'I', 'P', 'S' )
    
      ))
    
    select * from cte where Row = 1
    
    
    
    
    
    You can check other solutions at this blog post http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/including-an-aggregated-column-s-related
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by JeffS2002 Monday, March 7, 2011 9:31 PM
    Monday, March 7, 2011 9:10 PM
  • Naomi -

    That worked wonderfully! Thank you!

    Monday, March 7, 2011 9:31 PM