title: how to find duplicates and gaps in this scenario in mysql
body: <p>Hi I have a table that looks like</p>
  
  <pre><code>-----------------------------------------------------------
  |  id  |  group_id | source_id | target_id | sortsequence |
  -----------------------------------------------------------
  |  2   |    1      |    2      |   4       |     1        |   
  -----------------------------------------------------------
  |  4   |    1      |    20     |   2       |     1        |   
  -----------------------------------------------------------
  |  5   |    1      |    2      |   14      |     1        |   
  -----------------------------------------------------------
  |  7   |    1      |    2      |   7       |     3        |   
  -----------------------------------------------------------
  |  20  |    2      |    20     |   4       |     3        |   
  -----------------------------------------------------------
  |  21  |    2      |    20     |   4       |     1        |   
  -----------------------------------------------------------
  </code></pre>
  
  <p><strong>Scenario</strong></p>
  
  <p>There are two scenarios that needs to be handled. </p>
  
  <ol>
  <li><code>Sortsequence</code> column value should be unique against one <code>source_id</code> and <code>group_id</code>. For example if all the records having <code>group_id = 1 AND source_id = 2</code> should have sortsequence unique. In above example records having <code>id= and 5 which are having group_id = 1 and source_id = 2 have same sortsequence which is 1</code>. This is faulty record. I need to find out these records.</li>
  <li>If <code>group_id and source_id</code> is same. The <code>sortsequence columns value should be continous. There should be no gap</code>.  For example in above table <code>records having id = 20, 21 having same group_id and source_id and sortsequence value is 3 and 1</code>. Even this is unique but there is a gap in sortsequence value. I need to also find out these records.</li>
  </ol>
  
  <p><strong>MY So Far Effort</strong></p>
  
  <p>I have written a query </p>
  
  <pre><code>SELECT source_id,`group_id`,GROUP_CONCAT(id) AS children 
  FROM
      table 
  GROUP BY source_id,
    sortsequence,
    `group_id` 
   HAVING COUNT(*) &gt; 1 
  </code></pre>
  
  <p>This query only address the scenario 1. How to handle scenario 2? Is there any way to do it in same query or I have to write other to handle second scenario. </p>
  
  <p><code>By the way query will be dealing with million of records in table so performance must be very good.</code></p>