How to delete duplicate rows with different datetime in sql server

Raj Yadav December 31, 2013

DELETE  MobileInfo

FROM    MobileInfo b
JOIN    (
    SELECT  MIN(a.CreatedOn) CreatedOn ,MobileNo    
    FROM    MobileInfo a
    GROUP   BY  a.MobileNo
) c ON
 c.MobileNo = b.MobileNo
AND c.CreatedOn <> b.CreatedOn
and DATEDIFF(MINUTE,c.CreatedOn, b.CreatedOn)<15





Post a Comment

5 Comments

  1. The Writer BabaWednesday, January 01, 2014 4:51:00 PM

    Modified Version

    delete b
    from MobileInfo a join MobileInfo b on a.MobileNo = b.MobileNo
    where a.CreatedOn > b.CreatedOn
    and DATEDIFF(MINUTE,b.CreatedOn, a.CreatedOn) < 15

    ReplyDelete
  2. mithileshWednesday, January 01, 2014 5:03:00 PM

    delete from RawData a join RawData b on a.MobileNo = b.MobileNo where a.LOG_TIME > b.LOG_TIME
    and DATEDIFF(MINUTE,b.LOG_TIME, a.LOG_TIME) < 15

    ReplyDelete
  3. The Writer BabaWednesday, January 01, 2014 5:09:00 PM

    Mithilesh you are missing object name after delete. This query should be like

    delete b from RawData a join RawData b on a.MobileNo = b.MobileNo where a.LOG_TIME > b.LOG_TIME
    and DATEDIFF(MINUTE,b.LOG_TIME, a.LOG_TIME) < 15

    ReplyDelete
  4. Raj YadavThursday, January 02, 2014 12:27:00 PM

    Delete Duplicate Rows in sql server:

    delete b from MobileInfo a join MobileInfo b on a.MobileNo = b.MobileNo where a.CreatedOn > b.CreatedOn

    ReplyDelete
    Replies
    1. UnknownThursday, January 02, 2014 12:38:00 PM

      @Raj: This is not working for createdOn date time

      I have tried this:
      delete b from MobileInfo a join MobileInfo b on a.MobileNo = b.MobileNo where a.id > b.id

      This works fine for me :)

      Delete