remove duplicate record based on time updated..

Question

DocID | Date Updated | DocName

8245376 | 8/26/2009 1:07:01 PM | Doc1
8245376 | 8/26/2009 4:07:04 PM | Doc1
8245376 | 8/26/2009 7:07:01 PM | Doc2
8245376 | 8/26/2009 2:07:03 PM | Doc2
646681 | 8/26/2009 1:07:01 PM | Doc3
263272 | 8/26/2009 1:07:01 PM | Doc4
8245376 | 8/26/2009 1:07:01 PM | Doc5
8245376 | 8/26/2009 2:07:10 PM | Doc5
8245376 | 8/26/2009 1:07:01 PM | Doc6
8245376 | 8/26/2009 10:07:01 PM | Doc6
1903875 | 8/26/2009 1:07:01 PM | Doc7

From the above set of records, how to retrieve unique rows, based on date updated, ie, retrieve the most recently updated record?

thanks, Geetha

Answer 1

select * from table t1 join (select docid, max(dateupdated) as dateupdated from table group by docid) as t2 on t1.docid=t2.docid and t1.dateupdated=t2.dateudated

Answer 2

This assumes that you want to retain only the most recently updated per DocId. I am using the SQL Server DELETE statement, but I have to cheat a little bit by concatenating the relevant fields just so I can use a WHERE NOT IN clause. There are other ways, but this is fairly succinct.

DELETE
FROM Docs
WHERE CAST(DocId AS VARCHAR) + '_' + 
      CAST(Date_Updated AS VARCHAR) NOT IN 
(
  SELECT CAST(DocId AS VARCHAR) + '_' + 
         CAST(MAX(Date_Updated) AS VARCHAR)
  From Docs
  GROUP BY DocId
)

Answer 3

select distinct DocID, max("Date Updated") from mytable group by DocID