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] -----------------------------------------------------------------------------
