Skip to content
Related Articles

Related Articles

Improve Article

Find Duplicates in MS SQL Server

  • Last Updated : 08 Sep, 2020
Geek Week

We could use the following methods to find duplicate values in a table.

  • GROUP BY clause.
  • ROW_NUMBER() function.

1. Using the GROUP BY clause to find the duplicate values :

Syntax :

SELECT col1, col2, ...COUNT(*)
FROM table_name
GROUP BY col1, col2, ...
HAVING COUNT(*) > 1;



Example –
Let us create a table named Geek that contains three columns ID, A, and B.

CREATE TABLE Geek (
ID INT IDENTITY(1, 1),
A INT,
B INT,
PRIMARY KEY(id));



Let us add some values to the table Geek –



INSERT INTO Geek (A, B)
VALUES (1, 1),(1, 2), (1, 3), (2, 1),
(1, 2), (1, 3), (2, 1), (2, 2);



We know that Geek table contains the following duplicate rows –

(1, 2) (2, 1) (1, 3) 


MS SQL Server query to find the duplicate rows using GROUP BY clause in the Geek table :

SELECT A, B, COUNT(*) AS num
FROM Geek
GROUP BY A, B
HAVING COUNT(*) > 1;

Output –

Table – Geek

ABnum
212
122
132


To find the full row details for each duplicate row, JOIN the output of the above query with the Geek table using CTE :

WITH CTE AS ( 
SELECT A, B, COUNT(*) AS num
FROM Geek
GROUP BY A, B
HAVING COUNT(*) > 1
)
SELECT Geek.ID, Geek.A, Geek.B
FROM Geek
INNER JOIN CTE ON
CTE.A = Geek.A AND CTE.B = Geek.B
ORDER BY Geek.A, Geek.B;



Output –



Table – Geek

IDAB
212
512
613
313
421
721



2. Using ROW_NUMBER() function to find duplicate values :

Syntax :

WITH cte AS (
SELECT col,ROW_NUMBER() OVER (
PARTITION BY col
ORDER BY col) AS row_num
FROM table_name
)  
SELECT * 
FROM cte  
WHERE row_num > 1;


MS SQL Server query to find the duplicate rows using ROW_NUMBER() function in the Geek table :

WITH CTE AS (
SELECT A, B,
ROW_NUMBER() OVER (
PARTITION BY A, B
ORDER BY A, B
) AS rownum
FROM Geek
)
SELECT *
FROM CTE
WHERE rownum > 1;

Output –

Table – Geek

ABrownum
122
132
212

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :