April 5, 2004 at 1:40 pm
I have a statistics DB with data by Statdate (smalldatetime), ServerName (Char(50)).
There were some occasions where I ran the capture multiple times during a day. I want to eliminate everything but the last entry for each server for each day.
I can't figure out how to do it, at least without using cursors.
Any ideas ????
KlK
April 5, 2004 at 1:49 pm
Not sure if this will work:
delete from table t1
where Statdate != (select max(Statdate) from table t2 where t1.ServerName = t2.ServerName)
April 5, 2004 at 2:04 pm
try:
DELETE from Table1 t
WHERE Statdate != (SELECT MAX(Statdate) FROM Table1 t1
WHERE t.ServerName = t1.ServerName
AND CONVERT(VARCHAR(10),t.Statdate ,103) = CONVERT(VARCHAR(10),t1.Statdate ,103))
Francis
April 5, 2004 at 2:35 pm
Still testing, but I think fhanlon's trick did it.
This just goes to show that sometimes you need to NOT overthink the problem. I was working with 2 subselects joined and then some other things.
Technically for now I don't want to delete the extras rows, just filter them out.
But I think that's the code to do it.
Thanks
KlK
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply