Find Duplicates in MS SQL Server
Last Updated :
08 Sep, 2020
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
A |
B |
num |
2 |
1 |
2 |
1 |
2 |
2 |
1 |
3 |
2 |
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
ID |
A |
B |
2 |
1 |
2 |
5 |
1 |
2 |
6 |
1 |
3 |
3 |
1 |
3 |
4 |
2 |
1 |
7 |
2 |
1 |
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
A |
B |
rownum |
1 |
2 |
2 |
1 |
3 |
2 |
2 |
1 |
2 |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...