Hi, I've got the same kind of problem, though I need to keep the top 150 rows after a order has been done.
DELETE FROM table WHERE date='2003-07-08' ORDER BY count DESC LIMIT 150,-1; Though it doesn't seem to like the -1, but this works ... SELECT * FROM table WHERE date='2003-07-08' ORDER BY count DESC LIMIT 150,-1; Solaris 2.8 , mysql 4.0.13. Any ideas anyone ? Thanks Jerry ----- Original Message ----- From: "Steve Edberg" <[EMAIL PROTECTED]> To: "M Wells" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, April 16, 2003 11:28 AM Subject: Re: Delete all but (n) most recent records? > You should be able to use an order by & limit with delete, in recent > versions (>= 4.0, I think): > > DELETE FROM table ORDER BY id DESC LIMIT 200 > > See > > http://www.mysql.com/doc/en/DELETE.html > > for more info. I'm assuming that id would normally be an > autoincrement field, although this would work with a timestamp. This > would take care of any gaps in the id sequence. Normal warnings about > untested query apply... > > -steve > > > At 11:46 AM +1000 4/16/03, Daniel Kasak <[EMAIL PROTECTED]> wrote: > >Don Read <[EMAIL PROTECTED]> wrote: > > > >>SELECT (@top:=MAX(id)) FROM table; > >>DELETE FROM table WHERE id < (@top - 200); > >> > >>Regards, > >> > >No, you can't do that either. > >That assumes that you have 200 consecutive IDs. > >If someone has been deleting records, and causing gaps in the > >primary keys, then you will not get the desired result. > > > >-- > >Daniel Kasak > >IT Developer > >* NUS Consulting Group* > >Level 18, 168 Walker Street > >North Sydney, NSW, Australia 2060 > >T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > >email: [EMAIL PROTECTED] > >website: www.nusconsulting.com > > > > > -- > +------------------------------------------------------------------------+ > | Steve Edberg [EMAIL PROTECTED] | > | University of California, Davis (530)754-9127 | > | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | > +------------------------------------------------------------------------+ > | [EMAIL PROTECTED]: 1001 Work units on 23 oct 2002 | > | 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens... | > +------------------------------------------------------------------------+ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
