RE: [sqlite] Delete all other distinct rows
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] -
RE: [sqlite] Delete all other distinct rows
The solution was actually so simple, thanks. -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: 08 August 2007 12:01 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Delete all other distinct rows Andre, C:\Joinerysoft\JMS\dev\trunk>sqlite3 tst.db SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table tmp( id integer, name text ); sqlite> insert into tmp values( 1, 'some name' ); sqlite> insert into tmp values( 2, 'some name' ); sqlite> insert into tmp values( 3, 'some name' ); sqlite> insert into tmp values( 4, 'another name' ); sqlite> insert into tmp values( 5, 'another name' ); sqlite> sqlite> delete from tmp where id not in ( select min(id) from tmp group by name ); sqlite> sqlite> select * from tmp; 1|some name 4|another name sqlite> Rgds, Simon On 08/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > 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] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Delete all other distinct rows
Andre, C:\Joinerysoft\JMS\dev\trunk>sqlite3 tst.db SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table tmp( id integer, name text ); sqlite> insert into tmp values( 1, 'some name' ); sqlite> insert into tmp values( 2, 'some name' ); sqlite> insert into tmp values( 3, 'some name' ); sqlite> insert into tmp values( 4, 'another name' ); sqlite> insert into tmp values( 5, 'another name' ); sqlite> sqlite> delete from tmp where id not in ( select min(id) from tmp group by name ); sqlite> sqlite> select * from tmp; 1|some name 4|another name sqlite> Rgds, Simon On 08/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > 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] -