Handling the Duplicates using CTE and Row_Number() - (ie, To Delete or Update the Duplicates from SQL server Tables Using CTE, Row_Number() )
Just to select the duplicate rows from a table, we can use the following kind of simple query. The below mentioned query will return duplicated rows based on the Product_Id :
SELECT Product_id,
COUNT(Product_id) AS NumOccurrences
FROM InventoryDetail
GROUP BY Product_id
HAVING ( COUNT(Product_id) > 1 )
If you want to Delete or Update the duplicate, we can use the following sample which will use the CTE (Common Table Expression) , Row_Number function and the MDL statement (Delete/ Update).
WITH CTE (Product_id,Time_Id, DuplicateCount)
AS
(
SELECT Product_id,Time_Id,
ROW_NUMBER() OVER(PARTITION BY Product_id,Time_Id ORDER BY Product_id) AS DuplicateCount
FROM InventoryDetail
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
