title: SQL select multiple max rows where ID is same
body: <p>i've struggled again and again on this, but can't get it to work. been on these forums for hours now... Data set:</p>
  <pre>
  Row Date            AccountID   Column 1    
  1   21/02/2013 0:30     A0M8FA1003YP    .       
  2   21/02/2013 0:30     A0M8FA1003YP    .       
  3   21/02/2013 0:30     A0M8FA1003YP    .       
  4   24/09/2007 12:00    A0M8FA1003YP    .       
  5   24/09/2007 12:00    A0M8FA1003YP    .       
  6   24/09/2007 12:00    A0M8FA1003YP    .       
  7   12/02/2009 12:00    A023123332YP    .       
  8   24/09/2003 12:00    A023123332YP    .       
  9   24/09/2003 12:00    A023123332YP    .       
  10  24/09/2003 12:00    A023123332YP    .           
  </pre>
  <p>I want to return the max value of the date column, but not just return a single row, but any rows that match that max value. I.e. In the above set I want to return rows 1, 2, 3 and 7 (all columns for the rows as well).</p>
  <pre>
  Row Date                AccountID       Column 1    
  1   21/02/2013 0:30     A0M8FA1003YP    .       
  2   21/02/2013 0:30     A0M8FA1003YP    .       
  3   21/02/2013 0:30     A0M8FA1003YP    .   
  7   12/02/2009 12:00    A023123332YP    .       
  </pre>
  <p>I've got thousands of rows, and the number of matching rows to return for each ACCOUNTID will vary, some 1, some 2, some 10. Please help me!!!</p>
  <p><strong>UPDATE</strong>
  Have also tried this</p>
  <pre><code>Select max(ASS_SCH_DATE) over (partition by AccountID), 
         AccountID, 
         ASS_SCH_DATE, 
         ACCOUNTID 
  from #Temp3 
  order by #Temp3.ACCOUNTID 
  </code></pre>
  <p>Results still showing extra rows.</p>
  <pre>
  
  (No column name)            ASS_SCH_DATE                ACCOUNTID
  2013-02-21 00:30:00.000     2013-02-21 00:30:00.000     A0M8FA1003YP
  2013-02-21 00:30:00.000     2013-02-21 00:30:00.000     A0M8FA1003YP
  2013-02-21 00:30:00.000     2013-02-21 00:30:00.000     A0M8FA1003YP
  2013-02-21 00:30:00.000     2007-09-24 12:00:00.000     A0M8FA1003YP
  2013-02-21 00:30:00.000     2007-09-24 12:00:00.000     A0M8FA1003YP
  </pre>