SQL select multiple max rows where ID is same

i’ve struggled again and again on this, but can’t get it to work. been on these forums for hours now… Data set:

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    .           

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).

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    .       

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!!!

UPDATE Have also tried this

Select max(ASS_SCH_DATE) over (partition by AccountID), 
       AccountID, 
       ASS_SCH_DATE, 
       ACCOUNTID 
from #Temp3 
order by #Temp3.ACCOUNTID 

Results still showing extra rows.

(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

Answer

Query:

SQLFIDDLEExample

SELECT t1.*
FROM Table1 t1
WHERE t1.Date = (SELECT MAX(t2.Date)
                 FROM Table1 t2
                 WHERE t2.AccountID = t1.AccountID)

Result:

| ROW |                            DATE |    ACCOUNTID |
--------------------------------------------------------
|   1 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   2 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   3 | February, 21 2013 00:30:00+0000 | A0M8FA1003YP |
|   7 | February, 12 2009 12:00:00+0000 | A023123332YP |