Justin Justin - 3 months ago 21
SQL Question

How to remove duplicate accounts in SQL?

I am using SQL Server 2008 and I was wondering how to remove duplicate customers either from the table or exclude it in my query. An Account_ID can only have 1 product associated with it. And the account with the most recent purchase date is what should be showing. An example is below:

Account_ID, Account_Purchase, Purchase_Date
1 Product 1 1/1/2016
2 Product 1 1/2/2016
3 Product 2 1/5/2016
1 Product 3 3/12/2016
4 Product 3 1/5/2016


Ideally I would only see:

Account_ID, Account_Purchase, Purchase_Date
2 Product 1 1/2/2016
3 Product 2 1/5/2016
1 Product 3 3/12/2016
4 Product 3 1/5/2016


This should not show up because it is not the most recent purchase from account 1

Account_ID, Account_Purchase, Purchase_Date
1 Product 1 1/1/2016


Thank you all for help, folks!

Answer

Simply acquire the latest purchase_date using max and group by account_id. Then use inner join to get the other details from the acquired details.

SELECT TABLE_NAME.* FROM TABLE_NAME 
INNER JOIN(
SELECT Account_ID, MAX(Purchase_Date) AS Purchase_Date 
GROUP BY Account_ID
) LatestPurchases
ON TABLE_NAME.Account_ID = LatestPurchases.Account_ID
AND TABLE_NAME.Purchase_Date = LatestPurchases.Purchase_Date