Delete duplicate records using CTE, Sql Server

You may use following query :

WITH CTE(ORDER_ID,REQUEST_ID,ROW_No)
as
(
SELECT ORDER_ID,REQUEST_ID,ROW_NUMBER() OVER(PARTITION BY ORDER_ID ORDER BY ORDER_ID) AS ROW_No
FROM CRM.ORDER_COMPLIANCE WHERE ORDER_ID=397
)
SELECT ORDER_ID,REQUEST_ID,ROW_No FROM CTE
–DELETE FROM CTE WHERE ROW_NO>1

There are alternate ways also to achieve this.Share your ideas.

Reference: Narendra Singh (http://blog.sikarnarender.com)

Leave a Reply

Your email address will not be published. Required fields are marked *