根據最新的時間戳條目刪除重複項

I have a table and I'm trying to remove all the duplicate and keep the  
the rows that has the latest datestamp.

Here is the table:

email address       orgin_date   new_opt_in_date   datestamp
123@ax.tu            1900-1-1     1900-1-1          2016-3-15
123@ax.tu            1900-1-1     1900-1-1          2016-3-15
iron_man@metrix.com  2015-2-2     2016-12-26        2017-1-19
iron_man@metrix.com  2015-2-2     2016-12-26        2018-6-6
sleep@dort.st        2016-3-15    2016-3-151        2019-1-23
sleep@dort.st        2016-3-15   2016-3-151         2018-5-6

I'm trying to keep only the data that has the recent datestamp, delete the 
rest and hope that the
output will like this:
email address       orgin_date   new_opt_in_date   datestamp
123@ax.tu            1900-1-1     1900-1-1          2016-3-15
iron_man@metrix.com  2015-2-2     2016-12-26        2018-6-6
sleep@dort.st        2016-3-15    2016-3-151        2019-1-23

DELETE FROM `tablename` 
WHERE datestamp
NOT IN (
SELECT * FROM (
 SELECT MAX(datestamp) FROM tablename 
   GROUP BY emailaddress
) 
)    
but nothing it didn't work     

您可以使用 CTE 為每個電子郵件地址添加從最新日期戳開始的 1 的行號，然後刪除所有其餘的。

;WITH x AS 
(
 SELECT email, origin_date, new_opt_in_date, datestamp,
   rn = ROW_NUMBER() OVER (PARTITION BY email ORDER BY datestamp DESC)
 FROM dbo.tablename
)
DELETE x WHERE rn > 1;

引用自：https://dba.stackexchange.com/questions/239266