Geek Mama

Remove Duplicate Rows

June 12th, 2008

I often find the need to check for duplicates in the database. This is not an intuitive SQL command to create off the top of your head as it involves Group By and the rarely used Having clause. Since I’m always looking it up, I thought I’d post it here.

The code below will identify which rows have duplicate values based upon two primary key columns:

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1


If you need to go a step further and remove duplicate rows, there’s a great Microsoft blog article that will walk you through the process:

How to remove duplicate rows from a table in SQL Server

Entry Filed under: SQL