Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011

    Unanswered: Problem with delete duplicated data

    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.

  2. #2
    Join Date
    May 2008
    Assuming all your dates are unique for duplicates values of field2, this should do the trick:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts