Menu
I have a table with a varchar column, and I would like to find all the records that have duplicate values in this column. What is the best query I can use to find the duplicates?
946
Find Duplicate Row values in One ColumnSELECT col, COUNT(col) FROM table_name GROUP BY col HAVING COUNT(col) > 1; In the above query, we do a GROUP BY for the column for which we want to check duplicates. We also use a COUNT() and HAVING clause to get the row counts for each group.
Once you have grouped data you can filter out duplicates by using having clause. Having clause is the counterpart of where clause for aggregation queries. Just remember to provide a temporary name to count() data in order to use them in having clause.
You can't directly do that in MySQL because there is no function to urlencode or urldecode strings. You will have to create a User Defined Function to handle that process. Once you have that function just go for a simple group by with a having clause.
Do a SELECT with a GROUP BY clause. Let's say name is the column you want to find duplicates in:
SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1; This will return a result with the name value in the first column, and a count of how many times that value appears in the second.
63
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
