List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 7 2007 11:54pm
Subject:Re: Deleting pseudo-duplicates...
View as plain text  
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...
>
>
Thread
Deleting pseudo-duplicates...Vicente Lopez7 Apr
  • Re: Deleting pseudo-duplicates...Peter Brawley8 Apr