IntroductionWhile working with SQL Server you might come across some situation where you need to remove the duplicate rows from the table. Here is a simple query that will do the job for you very easily.
Behind The SceneHere I am taking a table named 'YourTableName' that contains only one column named 'value'. This column contains some duplicate data, those I need to remove by a script. The script is bellow. Just update the table name in the place of 'YourTableName' and the column name in place of 'value'.
DELETE FROM YourTableName WHERE YourTableName.%%physloc%% NOT IN(SELECT MIN(b.%%physloc%%) FROM YourTableName b GROUP BY b.value);
Now you have removed the duplicate names from the table, to check this just run the below query.
SELECT o.value FROM YourTableName o INNER JOIN ( SELECT value, COUNT(*) AS dupeCount FROM YourTableName GROUP BY value HAVING COUNT(*) > 1 ) oc on o.value = oc.value