How To Remove Duplicate Rows From SQL Server 2008

Introduction

While 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.

Download Source Code Here

Behind The Scene

Here 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

Happy Coding...