Q:

SQL Select the Columnid with a max column group by one column

I think this question is already answered but it didn't satisfy my question.
I'd like to select the id/s of the names group by the latest date value (MAX) in my table. Using a group by column Name and group by column Date, I must get the ID, Name, Date.
Here is my table
ID            Name           Date
---------------------------------------
1             Brent          2012-02-17
2             Ash            2012-08-02
3             Brent          2012-08-15
4             Harold         2012-09-30
5             Margaret       2012-10-10
6             Ash            2012-12-01
7             Harold         2013-02-14
8             Ash            2012-01-01
9             Brent          2013-05-11

Output must be:
ID            Name           Date
---------------------------------------
5             Margaret       2012-10-10
6             Ash            2012-12-01
7             Harold         2013-02-14
9             Brent          2013-05-11

I try this statement:
 SELECT 
    [ID], [Name], MAX([Date]) as [Date]
 FROM 
    [SampleTable]
 GROUP BY 
    [Name]

But I get this error:

Column 'ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

A:

you can use Window Function such as ROW_NUMBER() 
SELECT  a.ID, a.Name, a.Date
FROM
(
    SELECT ID, Name, Date,
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) rn
    FROM    TableName
) a
WHERE   a.rn = 1

if ID and Name is the same for every group, you can simply add Name in the GROUP BY clause.
GROUP BY ID, Name

