MYSQL query to find duplicate records based on first name and last name
<p>I have a user table with following columns and i have come up with a query to identify the duplicate records based on the first name and last name.</p>
<p>table structure</p>
<pre><code>CREATE TABLE IF NOT EXISTS `User` (
  `id` bigint(20) NOT NULL,
  `address1` text COLLATE utf8_unicode_ci,
  `mobile` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `firstName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '1 - female, 2-male, 0 - unknown',
  `zip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `countryCode` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `joinedDate` datetime DEFAULT NULL,
  `dateOfBirth` date DEFAULT NULL COMMENT 'User date of birth'
) ENGINE=MyISAM AUTO_INCREMENT=63561 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 
</code></pre>
<p>and my query</p>
<pre><code>SELECT id, firstName, lastName, mobile, email, count(*) as dupCount 
FROM `User` `t` 
WHERE firstName is not null and lastName is not null 
GROUP BY firstName,lastName HAVING count(*) &gt; 1 
ORDER BY t.joinedDate DESC
</code></pre>
<p>below is the screenshot of the result when i run the query</p>
<p><a href="https://i.stack.imgur.com/hqb9V.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hqb9V.png" alt="enter image description here" /></a></p>
<p>and my data set is here <a href="http://pastebin.com/Y5qsTcFc" rel="nofollow noreferrer">http://pastebin.com/Y5qsTcFc</a></p>
<p>sorry if paste bin is not working. try this <a href="https://drive.google.com/file/d/0B3gG6MG9uh7KdmRGT3d0NWg2aVk/view?usp=sharing" rel="nofollow noreferrer">https://drive.google.com/file/d/0B3gG6MG9uh7KdmRGT3d0NWg2aVk/view?usp=sharing</a></p>
<p>when there are records with email address is NULL, we have a problem. i am always getting this error.</p>
<h1>1062 - Duplicate entry 'Rachel-Besnainou' for key 'group_key'</h1>
<p><a href="https://i.stack.imgur.com/uoUpi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uoUpi.png" alt="enter image description here" /></a></p>
<p>Query i ran is:</p>
<pre><code>SELECT id, firstName, lastName, mobile, email, count(*) as dupCount 
FROM `User` `t` 
WHERE firstName is not null and lastName is not null 
GROUP BY firstName,lastName 
HAVING count(*) &gt; 1
</code></pre>
<p>I have no idea why this error comes up. any help appreciated</p>
<p><strong>Update 1</strong></p>
<p>i try to add this to my database and ran the query still it was the same</p>
<pre><code>SET SESSION max_heap_table_size=536870912;

SET SESSION tmp_table_size=536870912;
</code></pre>
<p>ref: <a href="https://stackoverflow.com/questions/26725772/duplicate-entry-for-key-group-key">Duplicate entry for key &#39;group_key&#39;</a></p>
<p><strong>Update 2</strong></p>
<p>I tried the same database in diff mysql and phpmyadmin version and it worked. so looks like my mysql version has a bug. my mysql version is <strong>5.6.26</strong></p>