Select newest records that have distinct Name column

sql

Solution

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

Problem

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

Original source

Related problems