Delete duplicate using "WITH"

WITH DUP_DATA AS


(

SELECT ServiceRequestid , WorkRecordID , COUNT(*) AS CNT

FROM OSWorkrecordfreeformfact WITH (NOLOCK)

GROUP BY ServiceRequestid , WorkRecordID

HAVING COUNT(*) > 1

)

--SELECT OS.ServiceRequestid , OS.WorkRecordID , OS.JobrunID

DELETE OS

FROM DUP_DATA D

INNER JOIN OSWorkrecordfreeformfact OS

ON D.ServiceRequestid = OS.ServiceRequestid AND D.WorkRecordID = OS.WorkRecordID

INNER JOIN OSWorkrecordfreeformfact OS1

ON OS.ServiceRequestid = OS1.ServiceRequestid AND OS.WorkRecordID = OS1.WorkRecordID

AND OS.JOBRUNID < OS1.JOBRUNID