max(datetime) - cannot get it to work

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

General SQL Server Forums

New to SQL Server Programming

max(datetime) - cannot get it to work

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-02-10 : 09:47:32

I want to update the column Status to have the value 'Active' where the latest date is in the column myDateTime grouped by myID

This does not seem to work, I get Active on all rows with the same myID.

UPDATE tbl
SET tbl.Status = 'Active'
FROM table1 AS tbl
right JOIN
(
select
ByggnadsID
,max(myDateTime) as myDateTime
from table1
group by myID
)
AS x
ON x.myID = tbl.myID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-10 : 09:51:09

[code]
UPDATE tbl
SET tbl.Status = 'Active'
FROM table1 AS tbl
right JOIN
(
select ByggnadsID
,max(myDateTime) as myDateTime
from table1
group by myID
) AS x ON x.myID = tbl.myID and x.myDateTime = tbl.myDateTime
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 09:53:50

if SQL 2005 just do,


UPDATE tbl
SET tbl.Status = 'Active'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY myID ORDER BY myDateTime DESC) AS Seq,Status FROM table1) tbl
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP

maevr
Posting Yak Master

169 Posts

Posted - 2010-02-10 : 10:01:08

Thank you for your help, cannot see how I could miss "and x.myDateTime = tbl.myDateTime".

khtan: I will try this method, but for now my problem is solved.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources