Rotating user stats

Supposing I have a table with the following schema:

int pitchId, int pitcherId, int pitchType, timestamp pitchTimeStamp

where we keep track of every pitch a pitcher has pitched. Now let's say, I want to only include the last 100 pitches and purge anything other than the last 100 pitches for each pitcherId. I know one possible solution is to cycle through each pitcherId in PHP and find the 100th pitchId in the past for that pitcherId and delete anything older than that.

However, I assume there must be a more efficient way of doing this, either through stored procedures or in my mind, preferably triggers.

Thanks!开发者_Python百科

You can just use an SQL statement like this.

DELETE FROM pitch p 
WHERE NOT (p.pitchid IN 
          (SELECT p2.pitchid FROM pitchid p2 ORDER BY p2.pitchid DESC LIMIT 100))