delete duplicate

  • well lets say I have four records in a table:

    Name Address Eff_date

    A Lane D 25-09-2007

    A Lane K 24-06-2006

    A Lane P 23-05-2007

    A Lane Z 24-08-2007

    I would like to remove every record for A except one with max date. What would the sql look like?

    thanks

  • Hi ,

    I would prob create a working table to start the ID's of the records witht he Max dates for each change in the name.

    I would then delete from the Main table where the ID's are not in the Working table.

    Does this make sense?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • The easiest way is to join the table to itself.

    Delete from MyTable

    from MyTable t1

    left outer join (Select Distinct Name, Max(Eff_Date)

    From MyTable

    Group By Name) t2

    on t1.Name = t2.Name

    Where t1.Eff_Date <> t2.Eff_Date

    Basically, the WHERE clause says "Delete it all where it doesn't match the JOIN conditions above".

    Or something similar to that. You'll want to play with this in a Dev environment first before you deploy it to production. Especially as you're deleting data.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Try this

    with cte

    as (select row_number() over(partition by name order by Eff_date desc) as rn

    from mytable)

    delete from cte

    where rn>1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply