RE: [sqlite] Delete all other distinct rows

[Tom Briggs]

   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

[Andre du Plessis]

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

[Simon Davies]

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