SQL选择ID相同的多个最大行

Question

我在这方面一次又一次地挣扎，但无法让它发挥作用。在这些论坛上呆了几个小时……数据集:

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

UPDATEHave also tried this

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

结果仍然显示额外的行。

(No column name)            ASS_SCH_DATE                ACCOUNTID2013-02-21 00:30:00.000     2013-02-21 00:30:00.000     A0M8FA1003YP2013-02-21 00:30:00.000     2013-02-21 00:30:00.000     A0M8FA1003YP2013-02-21 00:30:00.000     2013-02-21 00:30:00.000     A0M8FA1003YP2013-02-21 00:30:00.000     2007-09-24 12:00:00.000     A0M8FA1003YP2013-02-21 00:30:00.000     2007-09-24 12:00:00.000     A0M8FA1003YP

Answer 1

查询:

SQLFIDDLEExample

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

结果:

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