根据最新的日期戳条目删除副本

Question

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     

Answer 1

您可以使用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;