title: SQL: SELECT TOP 2 DATES FOR EACH GROUP ID
body: <p>I have a table with entries like the following and i'm struggling with grouping when a date is involved.</p>
  
  <pre><code>  id    ref    startDate     
   ----  ----- -------------  
     1    001     01-01-2013       
     2    001     03-02-2013       
     3    002     31-01-2013       
     4    002     15-01-2013       
     5    001     05-06-2013 ....      
  </code></pre>
  
  <p>What i'm trying to do is select the top 2 max dates for each ref so I get the following.</p>
  
  <pre><code>  id    ref    startDate     
   ----  ----- -------------  
     5    001     05-06-2013   
     2    001     03-02-2013       
     3    002     31-01-2013       
     4    002     15-01-2013  
  </code></pre>
  
  <p>I think it meant to go along the lines of..</p>
  
  <pre><code>SELECT *
  FROM   TABLE a
  JOIN   (SELECT startDate
          FROM   TABLE b
          JOIN   TABLE c
          ON     b.ref = c.ref AND b.startDate &gt;= c.startDate
          GROUP BY ref) AS d
  ON     a.ref = b.ref
  </code></pre>
  
  <p>I using SQL 2000 so partition is out of the question unfortunately.        </p>