Below is the explanation
WITH cte
AS
(
SELECT row_number () OVER ( PARTITION BY
BillofMaterialsId, MasterItemId, MemberItemId, MemberRevisionId ORDER BY BillofMaterialsId DESC ) AS ROW,
*
FROM BOMDetails_Stg
)
Above code will rank duplicate values . It means if you have below data
BillofMaterialsId MasterItemId MemberItemId MemberRevisionId
AAA BBB CCC DDD
AAA BBB CCC DDD
EEE FFF GGG HHH
Then row_number () function will rank first two rows as 1 , 2 and third row as 1.
So if we want to remove duplicate from above table then we need to delete data which has rownumber as 2
DELETE CTE
WHERE ROW > 1
If the first code WITH function will create CTE table with ROW_NUMBER as mentioned earlier . This query will remove data from actual table where ROW_NUMBER is greater than 1. Means it will remove duplicate row.