Re: Delete all rows in table but keep the last recent 2

[yvonne kire]

sort of right - this give me the 2 newest rows I like to keep for one user


Now I have millions of user_id where this would have to be applied to so I need to write some reoccur ant loop that takes care of it in one run without me interacting...

+-------------------------+-------------------+
| id | user_id | from_unixtime(create_date) |
+-------------------------+-------------------+
|28866 | 1978 | 2009-09-23 13:56:41 |
|28850 | 1978 | 2009-09-23 10:15:55 |
+-------------------------+-------------------+

I have the id that would be kept so any id other than this two should be deleted for this particular user_id.

Now - HOW do I use this info to DELETE all other rows that are older than the two I found?? ie any rows other than this two needs to be purged out from the table.
I store away this two values in a variable? and then delete from table where id not in (@variable1,@variable2) ?
or
[untested]
delete from tab where id in (select id from tab where user_id = XXX order by CREATE_DATE desc limit 2 );