all records with MAX(Date) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

all records with MAX(Date)

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-03-04 : 13:28:06

I have the following table layout:


ID      Name    Num      Date
---     ----    -----   ----------
1       cba     0.12    2011-01-10
2       cba     0.32    2011-01-14
3       efg     0.21    2011-01-11
4       cba     0.23    2011-01-17
5       tyu     0.47    2011-02-14

I need to retrieve those records that have the latest Date for each Name entry and update the Num value. I can retrieve the last date Name value with


SELECT Name, MAX(Date) FROM tbl GROUP BY  Name

but how would I combine this with an UPDATE statement?
Any guidance would be appreciated.

chtummala
Starting Member

4 Posts

Posted - 2011-03-04 : 14:01:41

update tbl
set [date] = k.[date]
from tbl t
inner join (select name,MAX(date) [date] from tbl group by name) k
on k.name= t.name

chandu

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-06 : 07:07:12

What is the value for Num column you want to set? Does name and date uniquely identify a row?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-03-08 : 10:20:30

quote:
Originally posted by mmarovic

What is the value for Num column you want to set? Does name and date uniquely identify a row?

The value for the Num column comes from a different table that has a unique entry for each Name.
In the table that I would like to update the Name and Date together uniquely identify a row.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-08 : 14:08:46

[code]
update t
set t.Num = dt.Num
from tbl t
join different_table dt
on dt.name = t.name
join (select name, max(date) as [date] from tbl group by name) agg
on agg.name = t.name and
agg.[date] = t.[date]
[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources