How to remove duplicate with different time

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 2000 Forums

Transact-SQL (2000)

How to remove duplicate with different time

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2011-11-23 : 09:52:11

Hello,

I have to remove some records in table that duplicate, but there is specific time in there....For example:

1968655 2011-11-21-16.18.48.390000
1968655 2011-11-21-17.52.20.387000

As above data, account=1968655 appeared twice in table with different time frame....I want to remove the firt one....How would I do it...

Data type for Datesent=Timestamp
and acount=varchar

thanks,

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-23 : 10:02:58

delete tbl
from tbl t1
join (select account, date = max(datesent) from tbl group by account having count(*) > 1) t2
on t1.account = t2.account
and t1.datesent <> t2.datesent

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ntn104
Posting Yak Master

175 Posts

Posted - 2011-11-23 : 10:16:57

Thanks much...it works with your below code....

quote:
Originally posted by nigelrivett

delete tbl
from tbl t1
join (select account, date = max(datesent) from tbl group by account having count(*) > 1) t2
on t1.account = t2.account
and t1.datesent <> t2.datesent

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources