title: SQL Server Get values of top three records and display in one row per person
body: <p>I am trying to get the values of the top three rows for a person to all display in one row.</p>
  
  <p>My data looks like this:</p>
  
  <pre><code>id       co_number  client_no  Client_name  taken_date    taken_value
  --------------------------------------------------------------------------
  270103   12         1111       John Doe     6/7/11 8:45 AM    108
  270100   12         1111       John Doe     5/3/11 10:49 AM   109
  270097   12         1111       John Doe     4/4/11 1:58 PM    109
  270094   12         1111       John Doe     3/1/11 9:04 AM    106
  270091   12         1111       John Doe     2/1/11 8:47 AM    105
  270088   12         1111       John Doe     1/4/11 9:10 AM    106
  270120   12       2222       Jane Smith    6/7/11 9:06 AM     215
  270117   12       2222       Jane Smith    5/3/11 2:01 PM     216
  270114   12       2222       Jane Smith   4/4/11 2:08 PM      214
  270111   12       2222       Jane Smith    3/1/11 9:27 AM     209
  270159   12       3333       John Adams    6/7/11 9:45 AM     205
  270156   12       3333       John Adams   5/3/11 2:12 PM      203
  270153   12       3333       John Adams    4/4/11 1:42 PM     202
  270150   12       3333       John Adams   3/1/11 10:32 AM     198
  </code></pre>
  
  <p>I want the data to display like this (Date1 being the most recent, then Date2, then Date3):</p>
  
  <pre><code>co#  Name      Date1             Value1 Date2             Value2 Date3             Value3
  -------------------------------------------------------------------------------------------
  12   John Doe  2011-06-07 08:45  108.0  2011-05-03 10:49  109.0  2011-04-04 13:58  109.0
  </code></pre>
  
  <p>Here is what I have so far.  It works but it's slow (takes 30 secs to return one co_number) so I'm wondering if there is a better more efficient way of doing this.</p>
  
  <pre><code>select 
  vmain.co_nmber, vmain.Client_name, vmain.Taken_date, vmain.Taken_value
  , (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date &lt; vmain.Taken_date order by vdate.Taken_date desc) as date2
  , (select top 1 Taken_value from vital v_value where v_value.co_nmber=vmain.co_nmber and v_value.Medical_Record_Number=vmain.Medical_Record_Number and v_value.Taken_date &lt; vmain.Taken_date order by v_value.Taken_date desc) as value2
  , (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date &lt; (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date &lt; vmain.Taken_date order by vdate.Taken_date desc) order by vdate.Taken_date desc) as date3
  , (select top 1 Taken_value from vital vvalue where vvalue.co_nmber=vmain.co_nmber and vvalue.Medical_Record_Number=vmain.Medical_Record_Number and vvalue.Taken_date &lt; (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date &lt; vmain.Taken_date  order by vdate.Taken_date desc)  order by vvalue.Taken_date desc) as value3
  from vital as vmain 
  inner join(
  SELECT v.co_nmber, v.Medical_Record_Number, max(v.Taken_date) as Taken_date
  FROM Vital v
  and v.co_nmber = 12
  GROUP BY v.co_nmber, v.Medical_Record_Number 
  ) as vsub on vsub.co_nmber=vmain.co_nmber and vsub.Medical_Record_Number=vmain.Medical_Record_Number and vsub.Taken_date = vmain.Taken_date
  and vmain.co_nmber = 12
  order by vmain.co_nmber, vmain.Medical_Record_Number, vmain.Taken_date
  </code></pre>
  
  <p>Help appreciated.</p>