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
How to delete duplicate rows with different datetime in sql server
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
5 Comments
Modified Version
ReplyDeletedelete 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
delete from RawData a join RawData b on a.MobileNo = b.MobileNo where a.LOG_TIME > b.LOG_TIME
ReplyDeleteand DATEDIFF(MINUTE,b.LOG_TIME, a.LOG_TIME) < 15
Mithilesh you are missing object name after delete. This query should be like
ReplyDeletedelete 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
Delete Duplicate Rows in sql server:
ReplyDeletedelete b from MobileInfo a join MobileInfo b on a.MobileNo = b.MobileNo where a.CreatedOn > b.CreatedOn
@Raj: This is not working for createdOn date time
DeleteI 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 :)