title: SQL Top 1 query on multiple columns
body: <p>I have a script which returns the following table. If I put the script in a subquery and give it a pseudonym, what script would generate the top row by EVENT_DATE for each CARE_ID? This has to be compatible with SQL2000. Thank you.</p>
  
  <pre><code>    CARE_ID EVENT_ID    EVENT_TYPE  EVENT_DATE
      3       18          B           13/07/2010 00:00
      78      11          C           27/07/2009 00:00
      78      9           T           28/07/2009 00:00
      151     49          T           21/03/2010 00:00
      217     102         C           30/03/2010 00:00
      355     111         C           16/07/2010 00:00
      355     56          T           17/07/2010 00:00
      364     774         C           23/08/2012 00:00
      369     117         C           28/07/2010 00:00
      631     74          T           15/01/2010 00:00
      631     148         C           02/02/2010 00:00
      1066    91          T           15/11/2010 00:00
      2123    280         T           10/07/2011 00:00
      2265    448         C           31/05/2011 00:00
      2512    183         B           04/02/2014 00:00
      2691    906         C           12/01/2014 00:00
      2694    307         T           15/06/2011 00:00
      2694    544         C           02/07/2011 00:00
      2892    85          B           19/12/2011 00:00
      2892    641         C           13/02/2012 00:00
      3038    660         C           09/08/2011 00:00
      3162    407         T           15/04/2012 00:00
      3178    780         C           01/09/2012 00:00
      3311    175         B           27/01/2014 00:00
      3344    869         C           01/10/2013 00:00
      3426    474         T           13/07/2013 00:00
      3606    479         T           03/01/2014 00:00
      3770    917         C           11/01/2014 00:00
  </code></pre>