MySQL: 5.1.48
Phase: the trade_id are duplicated in table tmp_xf_test
Purpose: select the record which the gmt_modified is minimum and the trade_id are duplicated.
Because MySQL get the record in order.
[cc lang='sql']select * from
(select id,trade_id,gmt_modified from tmp_xf_2
order by trade_id,gmt_modified) t
group by trade_id;[/cc]
Another way (more complicated)
[cc lang='sql']select b.* from (select trade_id, min(gmt_modified) as dt
from tmp_xf_test
group by trade_id) a, tmp_xf_test b
where a.trade_id = b.trade_id
and a.dt = b.gmt_modified;[/cc]
--
Purpose: delete the duplicated rows and keep one which the 'gmt_modified' is mininum
[cc lang='sql']delete a from tmp_xf_test a,
(select *, min(gmt_modified) as bid
from tmp_xf_test
group by trade_id
having count(1) > 1) as b
where a.trade_id = b.trade_id
and a.gmt_modified > b.bid;[/cc]
Have fun
This work is licensed under a CC A-S 4.0 International License.