You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named Productld, 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 duplicates 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 MAX(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
B. WITH CTEDupRecords
AS
(
SELECT MAX(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
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 p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
Correct Answer: B
Explanation/Reference:
--Burgos - NO
I changed answer to B (previous was A) because is imposseble to delete products with CreateDateTime greater than MAX(CreateDateTime). In fact will exists ONE AND ONLY ONE record with CreateDateTime EQUAL TO MAX(CreateDateTime), all records with same ProductName have a lower than MAX(CreateDateTime).
I tested both choices anda ONLY B is correct.
Use the code below to test (note that SELECT will catch only rows to be deleted:
--Exam A Q028
CREATE TABLE Products (
Productld int identity (1, 1) not null,
ProductName varchar (10) not null,
CreatedDateTime datetime not null,
constraint PK_Products PRIMARY KEY CLUSTERED (Productld)
)
GO
ALTER TABLE Products ADD CONSTRAINT UQ_Products UNIQUE (ProductName, CreatedDateTime)
GO
INSERT INTO Products (ProductName, CreatedDateTime) VALUES (‘Product 1’, ‘2010-10-10’)
INSERT INTO Products (ProductName, CreatedDateTime) VALUES (‘Product 1’, ‘2011-11-11’)
INSERT INTO Products (ProductName, CreatedDateTime) VALUES (‘Product 1’, ‘2012-12-12’)
INSERT INTO Products (ProductName, CreatedDateTime) VALUES (‘Product 2’, ‘2010-10-10’)
INSERT INTO Products (ProductName, CreatedDateTime) VALUES (‘Product 2’, ‘2012-12-12’)
INSERT INTO Products (ProductName, CreatedDateTime) VALUES (‘Product 3’, ‘2010-10-10’)
GO
WITH CTEDupRecords AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
select p.*
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
AND p.CreatedDateTime > cte.CreatedDateTime
GO
WITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
select p.*
FROM Products p
JOIN CTEDupRecords cte ON
cte.ProductName = p.ProductName
AND cte.CreatedDateTime > p.CreatedDateTime
GO
PS: In v.2012-10-17.by.Alex.142q this exercise appears with choice A using "<" instead of ">", so, in Alex we have two correct answers (A and B).
--Burgos
Verified answer as correct.