"None can stop the rising sun, clouds can hide for a while........" -Ravi

Tuesday, February 16, 2010

Duplicate rows in a table

Following code is used to find duplicate rows in a Table, which doesn't have primary key. You can use either GROUP clause or HAVING clause to get all duplicate rows.

HAVING Clause:

SELECT COLUMN_NAME
FROM TABLENAME
GROUP BY COLUMN_NAME
HAVING COUNT(*) >1

GROUP BY Clause:

SELECT * FROM (
SELECT COLUMN_NAME, COUNT(COLUMN_NAME) AS CNT
FROM TABLENAME
GROUP BY COLUMN_NAME) AS A
WHERE CNT>1