user2441297 user2441297 - 3 months ago 8
SQL Question

Removing duplicates with original records

I have this good SQL code that is selecting/removing duplicate records from table:

WITH CTE AS(
SELECT UserId, A, B,
RN = ROW_NUMBER()OVER(PARTITION BY A, B ORDER BY UserId)
FROM Users
) SELECT * FROM CTE WHERE RN > 1


But it's selecting all duplicates without the original record. How can I select duplicates + their originals (+1)?

Answer

I think you want to use count(*) rather than row_number():

WITH CTE AS (
      SELECT UserId, A, B, COUNT(*) OVER (PARTITION BY A, B) as cnt
      FROM Users
     )
SELECT *
FROM CTE
WHERE CNT > 1;