title: delete overflow record per ID
body: <p>i need to have 4 records or less per ID. some ID's have more than 4 records. I need to delete the records above the limit so i have 4 records per ID.<br>
  i tried many things but the only solution i found is just deleting all the records when the ID has more than 4. <br>this is my code for getting the ammount of records per ID:</p>
  
  <pre><code>select count(voorwerpnummer) AS plaatjes, voorwerpnummer
  from Illustraties INNER JOIN items 
  ON Illustraties.itemID = items.ID
  INNER JOIN tbl_voorwerp 
  ON items.ID = tbl_voorwerp.voorwerpnummer
  group by voorwerpnummer
  order by plaatjes DESC
  </code></pre>
  
  <p>i have this line to delete the extra records per itemID:</p>
  
  <pre><code>DELETE FROM illustraties 
  WHERE plaatjefile NOT IN (select top 4 plaatjefile from illustraties where itemID = 110769395358)
  AND itemID = 110769395358
  </code></pre>
  
  <p>now i need to itterate through all the itemID's which have more than 4 records.
  this is how to get all the itemID's with more than 4 records:</p>
  
  <pre><code>WITH cte AS
  (
     SELECT *,
       ROW_NUMBER() OVER (PARTITION BY itemID ORDER BY itemID) AS rn
     FROM illustraties
  )
  SELECT distinct ItemID
  FROM cte
  WHERE rn &gt; 4
  </code></pre>
  
  <p>can anyone make me a function or something to go through all those itemID's and execute that delete statement?<br>
  OR make a query which adds a rownumber per ID.<br> 
  for example : an ID has 5 records. the 5 records get the numbers 1 to 5. the next ID has 8 records. the 8 records get the numbers 1 to 8.<br>
  this way i can delete the records which have an rownumber of 5 or higher.</p>