locked
Sub select for the top 1 row RRS feed

  • Question

  • I am currently doing this:

    select  p.id,
            o.op_sys,
            o.op_date,
            first_muscle_relaxant = 
              (select top 1
                      med_name
                 from t_meds_fluids mf 
                where mf.op_sys     = o.op_sys 
                  and mf.dose_time  between dateadd(mi, -10, o.induction) and dateadd(mi, 30, o.induction) 
                  and med_name      in ('rocuronium', 'vecuronium', 'atracurium') 
                order by mf.dose_time
              ),
            administered_at = 
              (select top 1
                      mf.dose_time
                 from t_meds_fluids mf
                where mf.op_sys     = o.op_sys 
                  and mf.dose_time  between dateadd(mi, -10, o.induction) and dateadd(mi, 30, o.induction) 
                  and med_name      in ('rocuronium', 'vecuronium', 'atracurium') 
                order by mf.dose_time)
      from  t_operations o
      join  t_patients p 
            on o.patient_sys = p.patient_sys
     where  dept in ('gen','cri','csc') 
       and  o.op_date between '01/01/2005' and '12/31/2007'
    which just feels horribly incorrect and I want to do something like this

    select  p.id,
            o.op_sys,
            o.op_date,
            mf.med_name,
            mf.dose_time
      from  t_operations o
      join  t_patients p 
            on o.patient_sys = p.patient_sys
      join  top 1 t_meds_fluids mf
             on o.op_sys = mf.op_sys
             and mf.dose_time  between dateadd(mi, -10, o.induction) and dateadd(mi, 30, o.induction)
     where  dept in ('gen','cri','csc') 
       and  o.op_date between '01/01/2005' and '12/31/2007'
    but I know that's not possible.

    I would think that I am not the first person to want to do something like this and am wondering if there is a common pattern I should be using to accomplish this.

    Thanks

    m
    Monday, November 2, 2009 8:46 PM

Answers

  • Here you go...  Try this... (SQL2005 and above only):

    select  p.id,
            o.op_sys,
            o.op_date,
            first_muscle_relaxant,
            administered_at 
      from  t_operations o
      join  t_patients p 
            on o.patient_sys = p.patient_sys
      cross apply (select top 1 first_muscle_relaxant=mf.med_name
                               ,administered_at=mf.dose_time
                   from t_meds_fluids mf
                   where mf.op_sys     = o.op_sys 
                     and mf.dose_time  between dateadd(mi, -10, o.induction) and dateadd(mi, 30, o.induction) 
                     and med_name      in ('rocuronium', 'vecuronium', 'atracurium') 
                   order by mf.dose_time) x
     where  dept in ('gen','cri','csc') 
       and  o.op_date between '01/01/2005' and '12/31/2007'
    



    --Brad (My Blog)
    • Marked as answer by Zongqing Li Monday, November 9, 2009 7:00 AM
    Monday, November 2, 2009 10:57 PM

All replies