Open In App

How to print duplicate rows in a table?

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report
Let us consider below table.

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;

Last Updated : 21 Jun, 2018
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads