SQL Finding Duplicates on a Column Subset with Detail

From WikiOD
Revision as of 11:53, 15 June 2021 by Admin (talk | contribs) (Text replacement - "{{note| This article is an extract of the original Stack Overflow Documentation created by contributors and released under [https://creativecommons.org/licenses/by-sa/3.0/ CC BY-SA 3.0]. This website is not affiliated with Stack Overflow }}" to "{{note| Credit:Stack_Overflow_Documentation }}")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Remarks[edit | edit source]

  • To select rows with out duplicates change the WHERE clause to "RowCnt = 1"
  • To select one row from each set use Rank() instead of Sum() and change the outer WHERE clause to select rows with Rank() = 1

Students with same name and date of birth[edit | edit source]

WITH 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

This example uses a Common Table Expression and a Window Function to show all duplicate rows (on a subset of columns) side by side.