From: Peter Brawley
Date: April 7 2007 11:54pm
Subject: Re: Deleting pseudo-duplicates...
List-Archive: http://lists.mysql.com/mysql/206029
Message-Id: <46182F38.1030604@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Vicente,

A simple & fast way is via an exclusion join:

delete t1
from tbl t1
left join tbl t2 on t1.value=t2.value and t1.id>t2.id
where t2.id is not null;

To understand how that works, notice that the query

select t1.id
from tbl t1
left join tbl t2 on t1.value=t2.value and t1.id>t2.id
where t2.id is null;

returns row pairs matched on value values with minimum id values; 
therefore the query

select t1.id
from tbl t1
left join tbl t2 on t1.value=t2.value and t1.id>t2.id
where t2.id is not null;

returns all remaining rows, and those are the ones deleted by the query 
given.

PB


Vicente Lopez wrote:
> Hello,
>
> I have a table with this values:
>
> id    value    time
> 1        12    200704042112
> 2        12    200704042120
> 3        14    200704042125
> 4        14    200704042131
> 5        17    200704042140
> 6        14    200704042143
> 7        20    200704042145
> 8        20    200704042148
>
>
> I want to delete the rows with the same value of the preceding one, 
> the spected result
> looks like this:
>
> id    value    time
> 1       12        200704042112
> 3       14        200704042125
> 5       17        200704042140
> 6       14        200704042143
> 7       20        200704042145
>
>
> but I don't have any idea how make this select...
>
>