Find Duplicate Records in SQL Example
Let’s take a look at an example of how to find duplicate records in SQL. We will use the GROUP BY and HAVING clause to find duplicates in SQL.
First, let’s create a demo database and table which will be used in an example.
CREATE DATABASE w3wikiDatabase;
USE w3wikiDatabase;
CREATE TABLE Geeks(
GeekID INTEGER PRIMARY KEY,
GeekName VARCHAR(255) NOT NULL,
GeekRank INTEGER NOT NULL,
GeekSchool VARCHAR(255) NOT NULL
);
INSERT INTO Geeks
VALUES
(101, 'Nix', 2, 'Code Valley School'),
(102, 'Rutz', 4, 'Blue Chip School'),
(103, 'Shrey', 1, 'GCOEA School'),
(104, 'Ankx', 3, 'Round Robin Play School'),
(105, 'Ridz', 7, 'Dream School'),
(106, 'Mayo', 6, 'Silver Shining School'),
(107, 'Bugs', 5, 'Twinkle Star Convent'),
(108, 'Maria', 5, 'Code Valley School');
Output:
Demo Table
In the above table, we can see there are 2 records with the same geek rank of 5. GeekID 107 and GeekID 108 are having the same rank of 5. Now we need to find this duplication using SQL Query.
Finding Duplicates in SQL Example
In this example, we will find duplicate in column ‘GeekRank’
Query:
SELECT GeekRank, COUNT(GeekID) AS DuplicateRanks
FROM Geeks
GROUP BY GeekRank
HAVING COUNT(GeekRank)>1;
Output:
Explanation:
As we can see, the rows with duplicate GeekRank are grouped under the same GeekRank and their corresponding COUNT is the count of the GeekRank of duplicate rows. GeekID 107 and GeekID 108 are having the same rank 5. Thus in the above output, we could see GeekRank as 5 (because this 5th rank is found duplicated ), and since two GeekIDs were having the same GeekRank 5 so DuplicateRank i.e. count of the duplicate records is 2. Once you found the duplicate rows, you may choose to remove those duplicate rows using the DELETE statement.
How to Find Duplicate Records in SQL?
To find the duplicate records in SQL use the GROUP BY and HAVING Clauses.
Using the GROUP BY clause group all the rows of the target column, and by using the COUNT() function of the HAVING clause you can find groups that have more than one entry. Groups with more than one entry are the duplicate values.