You administer a Microsoft SQL Server database that includes a table named Products. The Products
table has columns named ProductId, ProductName, and CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime.
You need to modify the Products table to meet the following requirements:
Remove all duplic
ates of the Products table based on the ProductName column.
Retain only the newest Products row.
Which Transact-SQL query should you use?
A.
WITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON cte.ProductName = p.ProductName
AND cte.CreatedDateTime > p.CreatedDateTime
B.
WITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS
CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName
AND p.CreatedDateTime > cte.CreatedDateTime
C.
WITH
CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName
D.
WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE Products
FROM Products p
JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName
Explanation: