Find duplicate rows in SQL Server

Hi everyone.

In my last post, I imported data from Excel to SQL server. If we forgot to check duplicate rows in excel file, and loaded data in SQL Server. We have to find and delete duplicate rows in a table in SQL Server.

Check below query to find duplicate rows in table.

SELECT [column name]
    COUNT(*) occurrences
FROM [table name]
GROUP BY [column name]
HAVING
    COUNT(*) > 1;

Let me try this query on my table.

I have found one row with 2 occurrences, this row has duplicate values. Means there are two same rows, with exactly same records.

First, the GROUP BY clause groups the rows into groups by values in all columns.

Second, the COUNT() function returns the number of occurrences of each group (columns)

Third, the HAVING clause keeps only duplicate groups, which are groups that have more than one occurrence.
----------------------------

Now, Lets double check to query the database by searching this postcode and suburb name, and see it there are more than one records available.

Yes, there two same rows in table. We should now delete one row from database.

Thanks.
Rakesh.