title: MYSQL query to find duplicate records based on first name and last name
body: <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>