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.
| 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.
|
|
|
|
|
|
|
How to remove duplicate with different time