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