SHARE
TWEET

Untitled

a guest Jun 16th, 2019 64 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. I have a table and I'm trying to remove all the duplicate and keep the  
  2. the rows that has the latest datestamp.
  3.  
  4. Here is the table:
  5.  
  6. email address       orgin_date   new_opt_in_date   datestamp
  7. 123@ax.tu            1/1/1900     1/1/1900          3/15/2016
  8. 123@ax.tu            1/1/1900     1/1/1900          3/15/2016
  9. iron_man@metrix.com  2/15/2015    3/5/2015          6/6/2017
  10. iron_man@metrix.com  2/15/2015    3/5/2015          7/6/2018
  11. sleep@dort.st        2/15/2015    3/5/201           7/6/2018
  12. sleep@dort.st        2/15/2015    3/5/201           5/6/2018
  13.  
  14. I'm trying to keep only the data that has the recent datestamp, delete the
  15. rest and hope that the
  16. output will like this:
  17. email address       orgin_date   new_opt_in_date   datestamp
  18. 123@ax.tu            1/1/1900     1/1/1900          3/15/2016
  19. iron_man@metrix.com  2/15/2015    3/5/2015          6/6/2017
  20. sleep@dort.st        2/15/2015    3/5/201           7/6/2018
  21.  
  22. DELETE FROM `tablename`
  23. WHERE datestamp
  24. NOT IN (
  25. SELECT * FROM (
  26.   SELECT MAX(datestamp) FROM tablename
  27.     GROUP BY emailaddress
  28.  )
  29.  )    
  30. but nothing it didn't work
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top