RE: [sqlite] Delete all other distinct rows

2007-08-08 Thread 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

2007-08-08 Thread 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

2007-08-08 Thread 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]
-

[sqlite] Delete all other distinct rows

2007-08-08 Thread Andre du Plessis 
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