I don't think this would be very scalable, but you could do something like:
DELETE FROM table WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM table GROUP BY NAME ) This is totally untested, BTW - just a thought. :) -Tom > -----Original Message----- > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 08, 2007 5:30 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Delete all other distinct rows > > How to delete all other distinct rows except first one. > > > > If I have a table with rows > > > > ID, NAME > > > > 1, SOME NAME > > 2, SOME NAME > > 3, SOME NAME > > 4, ANOTHER NAME > > 5, ANOTHER NAME > > > > > > The delete should work even if you don't know what the value > of name is, > so simply for anything that is duplicate. > > > > The distinct delete should delete rows 2, 3, 5 and just keep > 1 and 4, is > there a single SQL statement that can achieve this? > > > > Thanks > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
