Name |
Section |
abc |
CS1 |
bcd |
CS2 |
abc |
CS1 |
In the above table, we can find duplicate row using below query.
SELECT name, section FROM tbl
GROUP BY name, section
HAVING COUNT(*) > 1
Another Example:
Given a table named PERSON task is to write an SQL query to find all duplicate name in the table.
Example :
+----+---------+
| Id | NAME |
+----+---------+
| 1 | Geeks |
| 2 | for |
| 3 | Geeks |
+----+---------+
Output :
+---------+
| NAME |
+---------+
| Geeks |
+---------+
The simple approach is to make a temporary table which have count of all the names in a table.
Duplicated NAME existed more than one time, so to count the times each NAME exists, we can use the following code:
select NAME, count(NAME) as num
from Person
group by NAME;
| NAME | num |
|---------|-----|
| Geeks | 2 |
| for | 1 |
This is a temporary table, on which we can run the below code to get duplicate NAME.
select NAME from
(
select NAME, count(NAME) as num
from Person
group by NAME
) as statistic
where num > 1;
The Best approach is to use GROUP BY and HAVING condition. It is more effective and faster then previous.
MySql :
select NAME
from Person
group by NAME
having count(NAME) > 1;
Share your thoughts in the comments
Please Login to comment...