Problem with delete duplicated data

gtotto · #1 (**permalink**) 10-28-11, 04:09

Hi to everyone.
First of all, sorry for my bad english..

Second, I have a problem with a query.

I have a table with some field like field1, field2, field3, field4 and some duplicated rows apart
for the value contained in the field4 that in my case in a type date.
I would like to select all the duplicated rows by the same value of "field2" , and then delete the one with the latest date.

Till now i just manage to write a query that select all the cuple of duplicated rows depending on the field2 , but then i don't how to erase (among the cuple) the rows with the latest date(field4).

SELECT * FROM table WHERE field2 IN
(select field2
FROM table
GROUP BY field2
HAVING count(*)=2) order by field2

I hope in your help.
Thanks in advance.

futurity · #2 (**permalink**) 11-01-11, 11:40

Assuming all your dates are unique for duplicates values of field2, this should do the trick:

Code:

delete from table as t1
using (
        select field2, min(field4)
        from table
        group by field2
        having count(*) > 1
    ) as t2
where
    t1.field2 = t2.field2
    and t1.field4 > t2.min

Internet Services

Web Development

Digital Marketing

Consumer Tech