title: MySql complex query | How to DELETE from SELECT and HOW to CREATE SUB-LIST FROM ARRAY
body: <p>Im working with this table:
  <a href="https://i.stack.imgur.com/CiNkV.png" rel="nofollow noreferrer">The Table I start with</a>
  and I have the next sql : </p>
  
  <pre><code>SELECT customer_id, 
     GROUP_CONCAT(incident_id order by create_time asc) as ids,
     count(incident_id) as total_count 
  FROM incidents 
  GROUP BY customer_id
  </code></pre>
  
  <p>this sql generates the following table
  <a href="https://i.stack.imgur.com/jF8py.png" rel="nofollow noreferrer">table</a></p>
  
  <p>I would like to perform a DELETE query on the generated table.
  I tried to do this : </p>
  
  <pre><code>DELETE FROM (
      SELECT customer_id, 
          GROUP_CONCAT(incident_id order by create_time asc) as ids,
          count(incident_id) as total_count 
      FROM incidents 
      GROUP BY customer_id)
  WHERE total_count &gt; 3 
  </code></pre>
  
  <p>BUT I received an error:
  <a href="https://i.stack.imgur.com/GeKxd.png" rel="nofollow noreferrer">error</a></p>
  
  <p>What am I doing wrong? </p>
  
  <p>And second question -->
  Once I will know how to delete from this table, I would like to extend the delete term ( which was "where total_count > 3" ) and add to it another term that will delete all the rows where ids are above 3 so I will be left with only 3 ids.
  In postgress I will do it by adding the next term :
  incident_id = ANY(ids[3:total_count])
  Meaning I want to create sub-list from the group.
  How do I do it in MySql?</p>
  
  <p>To sun it up, I would like to execute the following sql :</p>
  
  <pre><code>DELETE FROM (
      SELECT customer_id, 
             GROUP_CONCAT(incident_id order by create_time asc) as ids,
             count(incident_id) as total_count 
      FROM incidents 
      GROUP BY customer_id)
  WHERE total_count &gt; 3 
       AND incident_id = ANY(ids[3:total_count])
  </code></pre>