SQLTeam.com | Weblogs | Forums

Delete first record when dates are the same

tsql

#1

Unfortunately the dates are done in short dates and when it comes down to the records having the same dates and times, I need to delete the record that was entered first. Maybe by using min ID. Not sure how to do that Here is the SQL. I can't put data here

select twr.Id, GETDATE(),null, 1, 9, 'Terminated', 'Newer Agreement Became Effective. This was a system-initiated event.', 0
from tblTWRecord twr
join tblTWRecord twr2 on twr.EmployeeId = twr2.EmployeeId

and (twr.StartDate < twr2.StartDate
or (twr.StartDate = twr2.StartDate and twr.DateSubmitted < twr2.DateSubmitted))
where CAST(twr2.StartDate as date) <= CAST(GETDATE() as date) and twr.StatusId not in (8,9,10)
and (twr2.StatusId = 4 or twr2.StatusId = 11)


#2

Here is my template for deleting "all but the first" where there are "duplicates". You could use your ID column as tie-break for sorting

-- Delete duplicates TEMPLATE
-- BEGIN TRANSACTION
;WITH CTE_Delete
AS
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
		OVER (
			PARTITION BY MatchColumn1, MatchColumn2
			ORDER BY MatchColumn1, MatchColumn2,
-- ** NOTE: This Sort Order must present the row to RETAIN FIRST
				TieBreakColumn1, TieBreakColumn2
		)
	FROM	dbo.TableContainingDuplicates
	WHERE	MatchColumn1 LIKE 'SomeValue%'
)
DELETE	D
FROM	CTE_Delete AS D
WHERE	T_RowNumber >= 2
--
-- COMMIT
-- ROLLBACK

TAKE A BACKUP FIRST!!