Select newest records that have distinct Name column

Question

I did search around and I found this SQL selecting rows by most recent date with two unique columns Which is so close to what I want but I can't seem to make it work.

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

I want the newest row by date for each Distinct Name

Select ID,Name,Price,Date From  table Group By Name Order By Date ASC 

Here is an example of what I want

Table

ID	Name	Price	Date
0	A	10	2012-05-03
1	B	9	2012-05-02
2	A	8	2012-05-04
3	C	10	2012-05-03
4	B	8	2012-05-01

desired result

ID	Name	Price	Date
2	A	8	2012-05-04
3	C	10	2012-05-03
1	B	9	2012-05-02

I am using Microsoft SQL Server 2008

Answer 1

Select ID,Name, Price,Date From  temp t1 where date = (select max(date) from temp where t1.name =temp.name) order by date desc 

Here is a SQL Fiddle with a demo of the above

---

Or as Conrad points out you can use an INNER JOIN (another SQL Fiddle with a demo) :

SELECT t1.ID, t1.Name, t1.Price, t1.Date  FROM   temp t1  INNER JOIN  (     SELECT Max(date) date, name     FROM   temp      GROUP BY name  ) AS t2      ON t1.name = t2.name     AND t1.date = t2.date  ORDER BY date DESC  

Answer 2

There a couple ways to do this. This one uses ROW_NUMBER. Just partition by Name and then order by what you want to put the values you want in the first position.

WITH cte       AS (SELECT Row_number() OVER (partition BY NAME ORDER BY date DESC) RN,                  id,                  name,                  price,                  date           FROM   table1)  SELECT id,         name,         price,         date  FROM   cte  WHERE  rn = 1  

DEMO

Note you should probably add ID (partition BY NAME ORDER BY date DESC, ID DESC) in your actual query as a tie-breaker for date