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

I've a geniuine question, why does SQL make this operation this hard? seems a very common requirement.. — EralpB, Mar 04 '20 at 09:11

score 73 · Accepted Answer · edited Jan 08 '20 at 13:11

73

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

edited Jan 08 '20 at 13:11

Ashutosh Jindal

18,501
4
62
91

answered May 04 '12 at 16:59

Taryn

242,637
56
362
405

3

While this does work you can't create a tie-breaker without some field concatenation. Which is why I typically use an inner join instead [like this](http://sqlfiddle.com/#!3/e7382/7) when not using row_number() – Conrad Frix May 04 '12 at 17:05
1

@ConradFrix agreed, that was the second version that I was going to post, I just got this one done first. I will edit my answer with the second option as well. thanks for the suggestion. – Taryn May 04 '12 at 17:07
I went with the first part of this and it works percectly. Tie breaker will not be a concern since I am actually using a datetime, and the chances of two datetimes matching are less then the chances of me winning the lottery. I appreciate your help on this guys. – General Grey May 04 '12 at 17:24
1

The first SQL declaration above helped me out. Thanks for saving my life. – alvincrespo May 31 '17 at 13:46

score 21 · Answer 2 · answered May 04 '12 at 16:56

21

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

answered May 04 '12 at 16:56

Conrad Frix

51,984
12
96
155

This looks very complicated, I will give a go and report back how it works – General Grey May 04 '12 at 17:07
I am not saying this method doesn't work, but there are too many question Unanswered for me, I can't access the demo (work internet has blocked that page) and instead of bothering you guys with more questions I tried one of the other answers which seems to be working for me. +1 upvote since I have no doubt it works – General Grey May 04 '12 at 17:22
@K'Leg do you know why your work internet is blocking sqlfiddle? I ask because sqlfiddle.com is my site (I'll try not to take it too personally, heh). – Jake Feasel May 04 '12 at 18:47
They block all sites that have no category "This Websense category is filtered: Uncategorized." – General Grey May 04 '12 at 18:52
@JakeFeasel looks like you might be able to do something about it here http://www.websense.com/content/URLCategories.aspx – Conrad Frix May 04 '12 at 18:56
1

@JakeFeasel My Pleasure. And thanks for building SQLFiddle. As I've [said before](http://stackoverflow.com/questions/9916489/sql-rows-to-2-columns/9916741#comment12656059_9916741) its awesome. – Conrad Frix May 04 '12 at 19:24
@K'Leg great! Hope you find it useful. – Jake Feasel May 07 '12 at 17:11
Just a note to say that, if SQL Fiddle does not work for you try disabling uBlock (or your chosen adblocker). Also, consider supporting the site by whitelisting it in your adblocker settings. – Ashutosh Jindal Jan 08 '20 at 13:13

score 8 · Answer 3 · answered May 04 '12 at 17:01

8

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

answered May 04 '12 at 17:01

amit_g

30,880
8
61
118

score 0 · Answer 4 · answered Apr 11 '22 at 05:06

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

score -9 · Answer 5 · answered May 04 '12 at 16:58

-9

Use Distinct instead of Group By

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

http://technet.microsoft.com/en-us/library/ms187831.aspx

answered May 04 '12 at 16:58

fankt

1,007
1
7
16

Msg 145, Level 15, State 1, Line 9 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. – alexey Aug 28 '18 at 11:53

Select newest records that have distinct Name column

5 Answers5

Linked