0

I have a table with 4 columns : customerid , purchasedate, purchasenumber, and operationid. This table is returned as a result of another query.

I would like to eliminate any duplicate customers except the one that has the most recent date.

For example: If I have 4 entries with different purchasedates but the same operationid and same customerid then I would like to keep the most recent.

James Wiseman
  • 29,946
  • 17
  • 95
  • 158
user1327073
  • 1,004
  • 6
  • 14
  • 20

1 Answers1

1
DELETE FROM YourTable ;
  WHERE PurchaseDate < ;
    (SELECT MAX(PurchaseDate) ;
       FROM YourTable YT2 ;
       WHERE YT2.CustomerID = YourTable.CustomerID ;
         AND YT2.OperationID = YourTable.OperationID)

This should do it, but if you have multiple records for the same customerID and operationID on the most recent date, you'll keep all of them.

This is untested, so make sure to back up your data before trying it.

Tamar E. Granor
  • 3,817
  • 1
  • 21
  • 29
  • in case you're interested :) this worked for me very well : SELECT t1.* FROM mytable AS t1 LEFT OUTER JOIN mytable AS t2 ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date") WHERE t2.UserId IS NULL; source : http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column Thanks again – user1327073 Sep 21 '12 at 16:02