How to delete all duplicate rows but keeping one in sql

This post is on how to delete all duplicate rows but keeping one in sql.

In this post, I will explain how to delete duplicate records from a table in detail with example and proof for the said solution. This is a real time scenario that I have undergone personally in my day today work. To keep things simple, let’s consider with an example. You can apply the same logic by changing the column name for your table.

Scenario 

I have a table named “Account” which has “Id” as primary key and with other fields namely “AccountId”, “AccountName” and “CreatedDate”. I have duplicate “AccountId” data repeated in my table. To distinguish between records, they are inserted in different time interval (various date for understanding).

Now, I wanted to remove duplicate records and keep only latest record  for each “AccountId” in the table.

Steps

• Based on the use case specified above, I have a table created and data inserted.
• Based on the below image, you have two options to delete duplicates
  • You can retain first records for each “AccountId” or
  • You can retain latest record for each “AccountId”
• Base on the choice you make, you have to choose “Asc” or “Desc” as specified in the image below
  

SQL Code

DELETE FROM Account WHERE Id IN(
        SELECT Id FROM 
            (SELECT Id, ROW_NUMBER() OVER (PARTITION BY [AccountId] ORDER BY Id ASC)
                AS [ItemNumber] FROM  Account) 
            a WHERE ItemNumber > 1
        )
SELECT * FROM Account

DELETE FROM Account WHERE Id IN(
		SELECT Id FROM 
			(SELECT Id, ROW_NUMBER() OVER (PARTITION BY [AccountId] ORDER BY Id DESC) 
				AS [ItemNumber] FROM  Account) 
			a WHERE ItemNumber > 1
		)
SELECT * FROM Account

Now you can see the results after deleting the duplicate records. This shows how to delete all duplicate rows but keeping one in sql.

Thank you for reading my post and feel free to post your response in the comment section below. If you have any questions, you may post in the comment and I will surely reply to your comments.