title: Query returning one "extra" record. Any advice on how to remove it from the query results?
body: <p>We have the following, quite complex (at least for us) query.
  Since, as far as we know, there's no such thing as INTERSECT on MySQL, we are wondering how can we fix this:</p>
  
  <pre><code>( 
    SELECT GROUP_CONCAT(APA_T.district ORDER BY APA_T.district), t.name 
    FROM tbl_activity AS t 
     INNER JOIN tbl_activity_package AS ap ON t.id = ap.id_activity 
     INNER JOIN ( 
      SELECT DISTINCT apa.district AS district, ( 
       SELECT s1.id_activity_package
       FROM tbl_activity_package_address s1 
       WHERE apa.district = s1.district 
       ORDER BY s1.id DESC 
       LIMIT 1 
     ) AS idActivityPackage 
     FROM 
     tbl_activity_package_address apa 
     ORDER BY apa.district 
    ) AS APA_T 
    ON ap.id = APA_T.idActivityPackage 
    GROUP BY t.name 
    ORDER BY APA_T.district 
  ) 
  UNION DISTINCT 
  ( 
   SELECT GROUP_CONCAT(DISTINCT apa2.district ORDER BY apa2.district), t2.name 
   FROM tbl_activity AS t2 
   INNER JOIN tbl_activity_package AS ap2 ON t2.id = ap2.id_activity 
   INNER JOIN tbl_activity_package_address AS apa2 ON ap2.id = apa2.id_activity_package 
   GROUP BY t2.name 
   ORDER BY apa2.district 
  ) 
  #LIMIT 6, 6
  </code></pre>
  
  <p>Here are the results:</p>
  
  <pre><code>GROUP_CONCAT(APA_T.DISTRICT ORDER BY APA_T.DISTRICT)    NAME
  Beja,Faro,Setubal                                           activity-1
  Evora                                                   activity-2
  Sintra                                                  activity-4
  Braga,Sines                                                 activity-5
  Santarem                                                    activity-6
  Guarda,Matosinhos,Sagres                                    activity-7
  Lisboa,Montemor,Porto,Rio de Janeiro                    activity-8
  Beja,Evora,Faro,Setubal                                 activity-1
  Faro                                                    activity-3
  </code></pre>
  
  <p>Here are the results <strong>as we wish</strong> they were:</p>
  
  <pre><code>GROUP_CONCAT(APA_T.DISTRICT ORDER BY APA_T.DISTRICT)    NAME
  Beja,Faro,Setubal                                           activity-1
  Evora                                                   activity-2
  Sintra                                                  activity-4
  Braga,Sines                                                 activity-5
  Santarem                                                    activity-6
  Guarda,Matosinhos,Sagres                                    activity-7
  Lisboa,Montemor,Porto,Rio de Janeiro                    activity-8
  Faro                                                    activity-3
  </code></pre>
  
  <p><strong>ISSUE</strong></p>
  
  <p>This line should NOT appear. No activity should appear twice.</p>
  
  <pre><code>Beja,Evora,Faro,Setubal                                 activity-1
  </code></pre>
  
  <p>We understand that the <code>UNION DISTINCT</code> doesn't remove it, because indeed:
  <code>Beja, Faro, Setubal</code> IS DIFFERENT THAN <code>Beja,Evora,Faro,Setubal</code>
  HOWEVER, we wish <strong>NOT</strong> to have <code>Evora</code> to appear on the first result. So, <strong>it is OK as it is</strong>, the first query on the UNION does it's job as it should.</p>
  
  <p>Still, that second activity-1 that appears, should be removed. </p>
  
  <p>Any advice on how to solve this?</p>
  
  <p><strong>THE BIG PICTURE</strong>
  As you can see, this is quite a huge select that will, perhaps, get worst and slow by time.
  We wish to have a INFINITE SCROLL of Activities, and <em>the first results</em> of that Infinite Scroll, should be from Activities <em>happening on different districts</em>. Why? Why can't we do it "order by date" or something, you may ask.</p>
  
  <p>Because if a database back-end user do insert the last 20 records, all from one single district, we will have on the <em>infinite scroll first list results</em>, only activities from that district APPEARING that we don't have more than that district. </p>
  
  <p>So, the point is to LIST ALL the results on a certain (complex) ORDER. :)
  Any other, perhaps better way, would be great.</p>
  
  <p><a href="http://sqlfiddle.com/#!2/37dd94/51" rel="nofollow">http://sqlfiddle.com/#!2/37dd94/51</a></p>