Posted in SQL

MySQL : selecting duplicate entries

August 3, 2007 - No comment

This is the correct query :
SELECT field from `table` GROUP BY field HAVING COUNT(field) > 1;

Here’s an example where I have two duplicate entries (test and test3).

# let's see the records
mysql> SELECT field from `table`;
+-------+
| field |
+-------+
| test  |
| test  |
| test2 |
| test3 |
| test3 |
+-------+
5 rows in set (0.00 sec)

# THIS IS NOT WHAT WE WANT !!! THIS WILL ONLY RETURN THE FIRST ENTRY (duplicate or not)
mysql> SELECT field from `table` HAVING COUNT(field) > 1;
+-------+
| field |
+-------+
| test  |
+-------+
1 row in set (0.00 sec)

# This is what we want to do
mysql> SELECT field from `table` GROUP BY field HAVING COUNT(field) > 1;
+-------+
| field |
+-------+
| test  |
| test3 |
+-------+
2 rows in set (0.00 sec)

Thanks again to Marcel the Admin Blogger for some useful feedback on this article

Leave Comment

Please consider visiting the partners below if you enjoyed this article :

If this post saved you time and money, please consider checking my Amazon wishlist.

Before submitting, some rules :
- Is your comment related to the article ?
- You're having a problem ? Have you checked Google, other howtos, docs, manpages ?
- You're still having the problem ? Have you raised log verbosity, checked traces, ran tcpdump ?
- Have you checked your configuratoin for typo ?
Unless your comment is providing additional info or respect the rules above, DON'T comment.
If you don't understand what you are doing, I urge you to read the documentation, I'm not your free Level 1 helpdesk guy.