Finding Duplicates on a Column Subset with Detail:
*Students with same name and date of birth
Students with same name and date of birth
suggest changeWITH CTE (StudentId, Fname, LName, DOB, RowCnt)
as (
SELECT StudentId, FirstName, LastName, DateOfBirth as DOB, SUM(1) OVER (Partition By FirstName, LastName, DateOfBirth) as RowCnt
FROM tblStudent
)
SELECT * from CTE where RowCnt > 1
ORDER BY DOB, LNameThis example uses a Common Table Expression and a Window Function to show all duplicate rows (on a subset of columns) side by side.
Found a mistake? Have a question or improvement idea?
Let me know.