Count Duplicate Records – Rows

SELECT YourColumn, COUNT(*) TotalCount FROM YourTable GROUP BY YourColumn HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC
For Delete
------
delete 
FROM YourTable
WHERE ID NOT IN (SELECT MAX(ID) FROM YourTable GROUP BY YourColumn) and YourColumn=0 

delete

FROM Exam_Backup

WHERE ID NOT IN (SELECT MAX(ID) FROM Exam_Backup GROUP BY QuestionNo) and optionName=0

WITH CTE

WITH CTE (last_name,first_name,dcount) 

AS

(

SELECT last_name,first_name ,

ROW_NUMBER() OVER (PARTITION BY last_name,first_name ORDER BY last_name) AS dcount

FROM tbl_member 

)

SELECT * FROM CTE WHERE dcount > 1