'Select newest records that have distinct Name column

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

sql


Solution 1:[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

Solution 2:[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

Solution 3:[3]

select * from (
    Select
        ID, Name, Price, Date,
        Rank() over (partition by Name order by Date) RankOrder
    From table
) T
where RankOrder = 1

Solution 4:[4]

I have found another memory efficient way (but probably crude way)that has worked for me in postgress. Order the query by the date desc, then select the first record of each distinct field.

SELECT distinct on (Name) ID, Price, Date from
table
order by Date desc

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ashutosh Jindal
Solution 2 Conrad Frix
Solution 3 amit_g
Solution 4 jonah

Related Questions