how to delete the duplicate of rows...

[masterchicker]

I have table1 that contain multiple fields, two of which are DateModified and UniqueID. table1 contains duplicate entries, meaning they contain entries with the same UniqueID which is not supposed to be.

I wish to delete those duplicate entries and retaining only those entries which were modified the latest. How am I supposed to do this? I need your help, I'm just a newbie. Thanks...

 

[lechuck]

I suppose that UniqueID is not designed as unique in your table?

 

[Pattycake245]

There may be a cleaner way than this, but this works:

select *
into TempTable1
from Table1 a
where DateModified =
(select max(DateModified)
from Table1
where UniqueID = a.UniqueID)

delete Table1 from Table1 a where not exists(select * from Temptable1 b where a.uniqueid=b.uniqueid
and a.DateModified=b.DateModified)

I am assuming that your uniqueid is an int and your DateModfied is datetime.

Tim

 

[tlbroadbent]

You can also do the following using a correlated subquery.

Delete a
From TableName a
Where DateModified <>
 (Select Max(DateModified)
  From TableName b
  Where b.UniqueID = a.UniqueID)

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[masterchicker]

Thank you very much. Your replies did help me a lot.