In the following article we will go through a solution to a very common requirement of removing delete duplicate rows from SQL server table. Let us first create a table and add sample data to this table. Col1 in the table is an identity column.

CREATE TABLE #Table1(col1 int, col2 int, col3 char(50))
INSERT INTO  #Table1 values (1, 1, 'data value one')
INSERT INTO  #Table1 values (2, 1, 'data value dup')
INSERT INTO  #Table1 values (3, 2, 'data value two')

Now let us say we want to remove duplicates from above table based on values in col2, the below query will serve the purpose:

DELETE
FROM #Table1
WHERE col1 NOT IN
(
SELECT MAX(col1)
FROM #Table1
GROUP BY col2)

Now let us say we want to remove duplicates from above table based on values in col2 and col3 then the below query will serve the purpose:

DELETE
FROM #Table1
WHERE col1 NOT IN
(
SELECT MAX(col1)
FROM #Table1
GROUP BY col2,col3)