0
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     
2
  • Are you using Microsoft SQL Server, or MySQL? `tablename` is suspicious. – Aaron Bertrand May 29 '19 at 0:59
  • @Aaron Bertrand I'm using Microsoft SQL Server. I just typed the date to show an example. I hope this helps clarify my question. – learning May 29 '19 at 1:07
4

You can use a CTE to add a row number that starts at 1 from the newest datestamp for each e-mail address, then delete all the rest.

;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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.