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...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
