How to Find Duplicate Rows in SQL? Database: Standard SQL MySQL MS SQL Server PostgreSQL Oracle SQLite Operators: HAVING COUNT GROUP BY Table of Contents Problem: Example: Solution: Discussion: Problem: You have duplicate rows in your table, with only the IDs being unique. How do you find those duplicate entries? Example: Our database has a table named product with data in the following columns: id, name, and category. idnamecategory 1steakmeat 2cakesweets 3steakmeat 4porkmeat 5cakesweets 6cakesweets Let’s find duplicate names and categories of products. You can find duplicates by grouping rows, using the COUNT aggregate function, and specifying a HAVING clause with which to filter rows. Solution: SELECT name, category, FROM product GROUP BY name, category HAVING COUNT(id) > 1; This query returns only duplicate records—ones that have the same product name and category: namecategory steakmeat cakesweets There are two duplicate products in our table: steak from the meat category and cake from the sweets category. The first product is repeated two times in the table, while the second appears three times. Discussion: To select duplicate values, you need to create groups of rows with the same values and then select the groups with counts greater than one. You can achieve that by using GROUP BY and a HAVING clause. The first step is to create groups of records with the same values in all non-ID columns (in our example, name and category). You do this with a GROUP BY clause. After the GROUP BY keyword, you put the names of the columns you want to use for grouping. We exclude the id column because it’s our table’s primary key; by definition, each row will have a different value under that column. If we were to include it, then we would not be able to detect duplicates! We want to find groups with more than one row; such groups must contain a duplicate by definition, as long as we’ve grouped on the correct columns. To do this, we use a HAVING clause. The condition we specify is that the number of elements in the group—COUNT(id)—must be greater than one: COUNT(id) > 1. Remember that HAVING allows you to filter groups; WHERE is for filtering individual rows. Recommended courses: SQL Basics SQL Practice Set Recommended articles: SQL Basics Cheat Sheet The Best Way to Learn SQL: A Complete Guide for Beginners Where to Practice SQL 10 GROUP BY SQL Practice Exercises with Solutions Top 9 SQL GROUP BY Interview Questions See also: How to Delete Duplicate Rows in a Table in SQL Server How to Eliminate Duplicate Rows in SQL How to not Show Duplicates in SQL How to Filter Records with Aggregate Function COUNT Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query