In the above table, we can find duplicate row using below query.
SELECT name, section FROM tbl GROUP BY name, section HAVING COUNT(*) > 1
Given a table named PERSON task is to write an SQL query to find all duplicate name in the table.
+----+---------+ | 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.
select NAME from Person group by NAME having count(NAME) > 1;
This article is contributed by Sahil Rajput. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.
- What is Temporary Table in SQL?
- How to Get the names of the table in SQL
- Operations on table in Cassandra
- Cloning Table in MySQL
- PHP | MySQL ( Creating Table )
- SQL | Create Table Extension
- How to find Nth highest salary from a table
- Query to find 2nd largest value in a column in Table
- MySQL | Recursive CTE (Common Table Expressions)
- Altering a table to add a collection data type in Cassandra
- Difference between Aerospike and Alibaba Cloud Table Store
- SQL | Checking Existing Constraints on a Table using Data Dictionaries
- Check if Table, View, Trigger, etc present in Oracle
- Difference between Adabas and Alibaba Cloud Table Store
- Difference between Alibaba Cloud Table Store and Amazon DocumentDB
- Difference between Alibaba Cloud Table Store and Amazon Redshift
- Difference between Alibaba Cloud ApsaraDB for PolarDB and Alibaba Cloud Table Store
- Difference between Alibaba Cloud Log Service and Alibaba Cloud Table Store
- Print all even numbers from 1 to n in PL/SQL
- Print all odd numbers and their sum from 1 to n in PL/SQL